Cumulative sum even accross filtered data

[mauko]

We would like to analyze employee overtime with cumulative sum which would take into account all data even if they are filtered out by a slicer.  What I mean is that if an employee registers worktime from January 1st onwards, and by the end of March the cumulative overtime is x hours, when looking at the data for April the x would be taken into account as the "seed" number for April.  Now when I filter the worktime to only show April, the cumulative number starts from zero.  I tried to do this with a measure, but not lucky yet.  Is this possible?

[MFelix · Accepted solution]

Hi @mauko 

You need to use a measure and use CALCULATE and ALL to get the final result. something similar to:

Total Hours = CALCULATE (SUM(Table[Hours]), FILTER (ALL(Table[Date]),Table[Date] <= MAX (Table[Date]) )

[mauko]

Obrigado Miguel,

That worked.  I don't know what I was doing wrong before as I do think I already tried something like that.  One thing I find very difficult with PowerBI is to debug the measures...

[MFelix]

Hi @mauko ,

The debugging of measures can imply to have concatanex, checking the filtering of tables,ca n be done in several different ways but one approach that I always try to follow is to filter out the information using the external tools liem DAXSTUDIO and TABULAREDITOR.