Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

DAX Running Total

Hi all,   I have got a specific question about calculating a running total. I've reviewed the other messages about this subject but couldn't find a solution to my problem.   Please review below t...
General Comment
Need Help
Tips and Tricks
v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
9 years ago

Anonymous

 

In this scenario, when you reference a measure in CALCULATE(), the filter in that measure is applied on that entire specified table. So if you want to include those filter together into same CALCULATE(), you need to add ALL() on data table.

 

 

Regards,

Anonymous's avatar
Anonymous
Not applicable
9 years ago

v-sihou-msft

 

Just to see if I understand this correctly, what you're saying is that, while my original formula for AmntGlobal2 was:

 

RT AmntGlobal2:=IF (
    COUNTROWS ( data ) <> 0,
    CALCULATE (
        SUM ( data[Amount] ),
        FILTER ( data, data[Currency Type] = "20" ),
        FILTER ( ALL ( calendar ), calendar[DateKey] <= MAX ( calendar[DateKey] ) )
    )
)

 

It should actually have been:

 

RT AmntGlobal2:=IF (
    COUNTROWS ( data ) <> 0,
    CALCULATE (
        SUM ( data[Amount] ),
        FILTER ( ALL ( data[Currency Type]), data[Currency Type] = "20" ),
        FILTER ( ALL ( calendar ), calendar[DateKey] <= MAX ( calendar[DateKey] ) )
    )
)

 

First of all, this new filter on currency type solves the running total issue. The problem is that I still don't understand how this works. Let me explain.

 

If you recall, the problem with AmntGlobal2 was that it didn't calculate a running total amount, not that it didn't filter on currency type (this it did correctly). So I don't understand why a correction of the filter on currency type suddenly solves the running total problem.

 

Can you eleborate on this?

 

Rg. Erwin