topic Running Total by Date and Category in DAX Commands and Tips

Running Total by Date and Category

majans — Sun, 26 Jun 2022 08:55:06 GMT

Hi All,

After several hours studying running total by category/cumultative totals by category/accumulation patterns on this forum and others, I'm unable to find a solution to my problem 🙂

My current measure is:

Howver, when I add column "REFERENCE" which is a transaction type, the pattern breaks as below:

Re: Running Total by Date and Category

tamerj1 — Sun, 26 Jun 2022 09:37:26 GMT

H majans

please try

VAR CLosingBalance = CALCULATE ( SUM ( DAILY[QTY] ), REMOVEFILTERS ( 'DAILY' ), VALUES ( 'DAILY'[ITEMID] ), 'DAILY'[DATE] <= MAX ( 'DAILY'[DATE] ) )

Re: Running Total by Date and Category

majans — Sun, 26 Jun 2022 22:48:31 GMT

Hi tamerj1 ,

Tried your solution however I'm not getting desired result. See below CBTEST is current measure and CBTEST2 is your suggested solution.. Note that CBTEST gives correct result in first table however is not able to handle the additional Dimension "REFERENCE". Any ideas?

With REFERENCE DIMENSION

Re: Running Total by Date and Category

tamerj1 — Mon, 27 Jun 2022 03:32:21 GMT

majans

Please try

= SUMX ( FILTER ( CROSSJOIN ( ALL ( Dim[Reference] ), ALL ( 'DAILY'[DATED] ) ), Dim[Reference] = MAX ( Dim[Reference] ) && DAILY[DATE] <= MAX ( DAILY[DATE] ) ), CALCULATE ( SUM ( DAILY[QTY] ) ) )

Re: Running Total by Date and Category

majans — Mon, 27 Jun 2022 09:54:36 GMT

Thank you tamerj1 I have tried your suggested measure as below:

SUMX ( FILTER ( CROSSJOIN ( ALL ( REFERENCE[REFERENCE] ), ALL ( 'DAILY'[DATE] )), REFERENCE[REFERENCE] = MAX ( REFERENCE[REFERENCE] ) && DAILY[DATE] <= MAX ( DAILY[DATE] ) ), CALCULATE ( SUM ( DAILY[QTY] ) ) )

Resulting in below:

Should it make a difference that there are other Dimensions in this table like ITEMID, PLANVERSION, WAREHOUSEID etc?

Re: Running Total by Date and Category

tamerj1 — Mon, 27 Jun 2022 10:40:35 GMT

majans
Could be but before investigating further please try

= SUMX ( FILTER ( CROSSJOIN ( ALL ( REFERENCE[REFERENCE] ), ALL ( 'DAILY'[DATE] ) ), DAILY[DATE] <= MAX ( DAILY[DATE] ) ), CALCULATE ( SUM ( DAILY[QTY] ) ) )