DAX Calculted column with running total / cumulative with duplicate dates

[xl0911]

I have this table:

ID | ClientID | Date(dd/mm/yyyy)| Amount
-----------------------------------------------
6 | 1             | 01/01/2021            | 100
8 | 1             | 01/01/2021            | 150
3 | 1             | 02/01/2021            | 50
1 | 1             | 09/01/2021            | 200
5 | 2             | 03/01/2021            | 100
4 | 2             | 06/01/2021            | 50
7 | 2             | 07/01/2021            | 100
3 | 2             | 07/01/2021            | 50
9 | 2             | 07/01/2021            | 200

I need a running total base on the ClientID and the date but if there is a duplicate date I want to Sum the smaller ID.

Expected result:

ID | ClientID | Date(dd/mm/yyyy)| Amount |Running Sum
-------------------------------------------------
6 | 1             | 01/01/2021            | 100        | 100
8 | 1             | 01/01/2021            | 150        | 250
3 | 1             | 02/01/2021            | 50          | 300
1 | 1             | 09/01/2021            | 200        | 500
5 | 2             | 03/01/2021            | 100        | 100
4 | 2             | 06/01/2021            | 50          | 150
7 | 2             | 07/01/2021            | 100        | 300
3 | 2             | 07/01/2021            | 50          | 200
9 | 2             | 07/01/2021            | 200        | 500

I tried somthing like that without any luck:

RunningTotal =
var ClientIDValue = TBL[ClientID]
var DateValue = TBL[Date]

var FilterTbl = FILTER(TBL,TBL[ClientID] = ClientIDValue && TBL[Date] <= DateValue)

RETURN

CALCULATE(
sum(TBL[Amount])
,FilteredTbl
)

[MFelix · Accepted solution]

Hi @xl0911 ,

On the query editor sort you data by:

• ClientID
• Date
• ID

Then add and index column now do your column in the following way:

CUMULATIVE =
CALCULATE (
    SUM ( 'Table'[ Amount] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[ ClientID ] = EARLIER ( 'Table'[ ClientID ] )
            && 'Table'[ Date(dd/mm/yyyy)] <= EARLIER ( 'Table'[ Date(dd/mm/yyyy)] )
            && 'Table'[Index] >= EARLIER ( 'Table'[Index] )
    )
)

[xl0911]

Is it possible to do it without the query editor, only with pure DAX ?

[MFelix]

Hi @xl0911 ,

Since your value is based on dates and ID try the following:

• Add a column for ranking:

Ranking = RANKX('Table', FORMAT('Table'[ Date(dd/mm/yyyy)], "###")*10000 + 'Table'[ID ], ,ASC)

Be aware that depeding on the total value of your ID you may need to multiply by more that 10000 in order that your calculation gets the correct maximum values.

• Add another column for cumulative:

CUMULATIVE = 
CALCULATE (
    SUM ( 'Table'[ Amount ]),
    FILTER (
        ALL('Table'),
        'Table'[ ClientID ] = EARLIER ( 'Table'[ ClientID ] )
            && 'Table'[Ranking] <= EARLIER ( 'Table'[Ranking] ) 
            
    )
)

Result below:

Has you can see the column CUMULATIVE is equal to the Running Sum that I added from your table to show comparision.