topic Re: cumulative column not working correctly with values taking place on same date in DAX Commands and Tips

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

gmaasz — Wed, 01 Nov 2023 09:33:37 GMT

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 🙂

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

some_bih — Thu, 02 Nov 2023 08:58:49 GMT

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

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

gmaasz — Thu, 02 Nov 2023 13:07:40 GMT

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.

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

FreemanZ — Thu, 02 Nov 2023 16:52:36 GMT

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]

)

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

FreemanZ — Thu, 02 Nov 2023 16:54:46 GMT

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]

)

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

gmaasz — Fri, 03 Nov 2023 07:20:50 GMT

My date table is not being picked up.

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

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

FreemanZ — Tue, 07 Nov 2023 01:45:31 GMT

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.

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

gmaasz — Tue, 07 Nov 2023 06:29:59 GMT

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.

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

Dangar332 — Tue, 07 Nov 2023 08:13:35 GMT

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

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

gmaasz — Tue, 07 Nov 2023 11:54:46 GMT

Worked like a bomb thanks