DAX Running Total

[Erwin]

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 table called "data".

As you can see this table has a column called "Posting date" of the Date type. Therefore I have also added a date table called "calendar" and created a relationship between 'data'[Posting date] and 'calendar'[DateKey].

In order to calculate the global amount I've created a measure called "AmntGlobal". The DAX formula is:

AmntGlobal :=
CALCULATE ( SUM ( data[Amount] ), FILTER ( data, data[Currency Type] = "20" ) )

This is the output of "AmntGlobal":

Now I want to create a running total of "AmntGlobal" called "RT AmntGlobal". I have two options to do this.

1. I can build "RT AmntGlobal" by using "AmntGlobal". This method results in the following formula:
   

   RT AmntGlobal1 :=
   IF (
       COUNTROWS ( data ) <> 0,
       CALCULATE (
           [AmntGlobal],
           FILTER ( ALL ( calendar ), calendar[DateKey] <= MAX ( calendar[DateKey] ) )
       )
   )

   
   This is the output of "RT AmntGlobal1":
   
   
   Conclusion: This method works fine!
2. I can also build "RT AmntGlobal" completely stand-alone, so by not using "AmntGlobal". This method results in the following formula:
   

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

   
   This is the output of "RT AmntGlobal2":
   
   
   Conclusion: This method does not work!

I can't figure out why the "stand-alone" method doesn't work. I know that consecutive filter statements work as an AND statement, giving the intersection of the statements as the combined result. I just can't figure out why the placement of filter statements (both in one formula vs one in each) can have this effect.

Can you please provide insight?

Many thanks in advance!

Rg. Erwin

[v-sihou-msft]

@Erwin

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,

[Erwin]

@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