cumulative column not working correctly with values taking place on same date

[gmaasz]

Hi, I have a list of payments in a table that I would like to cumulatively add up in a column based on the payment date. I am using the following query:

Running Total COLUMN = 
CALCULATE (
    SUM ( 'Application Payments'[AmountPaid] ),
    ALL ( 'Application Payments' ),
    'Application Payments'[PaymentDate].[Date] <= EARLIER ( 'Application Payments'[PaymentDate].[Date] )
)

You can see in the image below that when there are 2 records of data on the same date, then it doesn't work properly and it causes the value in the graph to jump up, and then again back down.

Please help 🙂

[Dangar332 · Accepted solution]

Hi, @gmaasz 

try below code

just adjust table and column name

Measure = CALCULATE(
            SUM('Table'[amount paid]),
            'Table'[Column1]<MAX('Table'[Column1]),
            REMOVEFILTERS('Table'[amount paid])
        )+SUM('Table'[amount paid])

[gmaasz]

Worked like a bomb thanks

[some_bih]

Hi @gmaasz put Date column from your Calendar / Date table (should be unique)

[gmaasz]

I created a unique date column, and then updated my cumulative column formula as follows:

Running Total COLUMN = 
CALCULATE (
    SUM ( 'Application Payments'[AmountPaid] ),
    ALL ( 'Application Payments' ),
    'Date Table'[Date] <= EARLIER ('Application Payments'[PaymentDate] )
)

the table values still show the same issue.

[FreemanZ]

or you plot a table visual with date[date] column and a measure like:

Running Total Measure=

SUMX(

     FILTER(

        'Application Payments',

        'Application Payments'[PaymentDate]<=MAX('Date Table'[Date])

),

   'Application Payments'[AmountPaid]  

)

[FreemanZ]

hi @gmaasz ,

Add the column in your data table, like

Running Total COLUMN=

SUMX(

     FILTER(

        RELATEDTABLE('Application Payments'),

        'Application Payments'[PaymentDate]<='Date Table'[Date]

),

   'Application Payments'[AmountPaid]  

)

[gmaasz]

My date table is not being picked up.

I created my date table separately with a formula giving me the following style output

[FreemanZ]

hi @gmaasz ,

there was a typo. the column is supposed to add in your date table. It that so? You may also try the measure mentioned in another reply. 

[gmaasz]

Thanks my result is still not correct when using the measure:

Not sure where the typo is you are referring to? 

Maybe the issue is with my relationships? I have a 1 to many relationship with the date table and my application payments table.