calculate cumulative sum until the selected date (represented by filter drop down list)

Question

I'm traying to calculate cumulative sum for a Project until date selected in the filter. My filter date is a drop down list and it shows like this for example: July 2020 Aug 2020 .....

I have 2 tables: factworkinghours table and dimdates table. enter image description here

and I want to calculate cumulative sum of total hours per Project. for example if I have project #714 and I know this project took place from Jan 2020 until Fab 2020:

enter image description here

I want to get those result:

1. If my filter is on jan 2020 - I want to get 15.5 hours
2. If my filter is on fab 2020- I want to get 20.5 hours ( 15.5+5)
3. If my filter is on Mar 2020- I want to get 20.5 hours since the Project ended and no total hours was added.
4. If my filter is on Dec 2019 I want to get 0 since the project hasn't started yet.

I tried to do write this dax but it's not working:

test hadar = VAR _maxdate = CALCULATE ( MAX ( dimDates[date]) ) // max date of the selected dates return CALCULATE ( [sum of total hours], FILTER ( all( dimDates), dimDates[date] <= _maxdate ))

hope this is understood ... Thanks!

Answer 1

You'll need to create a further table of dates to use in the slicer. This could be a new Calculated Table, SlicerDates, say, which is identical to dimDates and generated by

SlicerDates = dimDates

Your slicer can then reference this table, choosing e.g. Month and Year from the hierarchy.

The required measure is then:

MyMeasure =
VAR SelectedMonthEnd =
    EOMONTH ( MIN ( SlicerDates[Date] ), 0 )
RETURN
    CALCULATE ( SUM ( 'Table'[TotalHours] ), dimDates[Date] <= SelectedMonthEnd )