I am going crazy trying to solve this puzzle: I need to calculate cumulative totals withing a giving period of time (Month Year Slicer). I have figure out this measure:
Cumulative VALUE = VAR _Lastdate = CALCULATE( LASTDATE('FactTable'[Date] ); ALL('FactTable') ) VAR _Firstdate = CALCULATE( STARTOFYEAR('FactTable'[Date] ) ) RETURN IF( SELECTEDVALUE( CALENDAR[Date] ) > _Lastdate; BLANK(); CALCULATE( SUM( [ColumnA]; FILTER( ALL( CALENDARIO ); CALENDARIO[Date] >= _Firstdate && CALENDARIO[Date] <= MAX( CALENDAR[Date] ) ) )
It works fine when the fact table have values for the specific MonthYear selected on the slicer. Problem is when fact table doesn’t have any value for than giving period of time. In this case, measures gives me back whole total instead of cumulative.
Thanks in advanced