Running Total by Date and Category

[majans]

 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:

[tamerj1]

H @majans 

please try

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

[majans]

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

[tamerj1]

@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] ) )
)

[majans]

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?

[tamerj1 · Accepted solution]

@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] ) )
)