Running Total in DAX -> ignoring other filters

[MiKeZZa]

I've made a DAX measure

cumulative months per client =

CALCULATE( DISTINCTCOUNT('fact table'[unique clients per month] ) ,

FILTER(

ALL('date'[date]),

'date'[date] <= MAX('date'[date])))

This works great and gives by example:

Filter: yearmonth = 201801

Cust    Dim field 1    Value

42       Option 1       10

42       Option 2       4

Now we want a measure that gives by example 12; that is calculated as 10 + 4 but 2 months are overlapping each other. So it must be ignoring any chosen fields from any of the dimensions except of cause client and date.

We can achieve what we want by doing this in DAX:

cumulative months total duration per client =

CALCULATE([cumulative months per client],ALL(dim[dim field 1]))

But this is definitely not the way; we have to call every object in every dim. That's not the solution we guess. But using an ALL doesn't the trick either; because of it is ignoring the client and date. So it gives us a total over all clients and dates.

So we're almost there but we miss the last percent.

[MiKeZZa · Accepted solution]

I've solved this:

cumulative months total per client =

if([cumulative months per client]=0,BLANK(),

CALCULATE([cumulative months per client],ALLEXCEPT('table','client'[clientid],'date'[month year]))

)