Cumulative or Rolling total issue

[Pbi07]

I am trying to do a simple cumulative total using the below measure and having trouble.  The cumulative total is not adding up if i try the DATESMTD or DATESYTD functions. What am i missing 

cumulative Total = calculate(sum(Trans[Amount]),DATESYTD('calendar'[Date]))

Date	Serial	Amount	cumulative Total
2022-01-10	588	1200	1200
2022-01-20	722	1400	1400
2022-02-02	820	2500	2500

Expected result is 

Date	Serial	Amount	cumulative Total
2022-01-10	588	1200	1200
2022-01-20	722	1400	2600
2022-02-02	820	2500	5100

PBix is  https://drive.google.com/file/d/12kfwDvcXWNX3FTWLcOLNBCscRmsGPyoO/view?usp=sharing

Thanks

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Pbi07]

Thanks @Ashish_Mathur 

The total breaks if i have the serial not in the seqeuntial order. 

I tried ignoring the serial, but then if there are multiple serials for the same day the total repeats. 

Also i modified the total to be the fiscal year total that ends 04/30. 

Attached my pbix - https://drive.google.com/file/d/1qX1zUOhgj_NeSd-IvNaIlBtt6zccJJhT/view?usp=sharing

Trying the fiscal total that ends at 04/30. If i run report upto sept 2021, total will be from May to Sep 2021

[davehus]

Hi @Pbi07 ,

Because you have the serial in the table, this is causing the issue with the RT not working for you. Please use the measure below, it should work.

cumulative Total = calculate(sum(Trans[Amount]),ALL(Trans),DATESYTD(Trans[Date]))

Use the date column from the trans table in the matrix.

Did I help you today? Please accept my solution and hit the Kudos button.

[Pbi07]

Thanks @davehus . Appreciate the help

That worked. Only issue is if i have the serials for same day, the cumulative totals repeats. How can it be treated as mor as running totals

1400 & 1000 for Jan 20th, results in 3600 repeated for the day

[tamerj1]

Hi @Pbi07 
Refer to the sample file attached 

cumulative Total = 
VAR CurrentDate =
    MAX ( 'calendar'[Date] )
VAR CurrentSerial =
    MAX ( Trans[Serial] )
VAR Result =
    CALCULATE ( 
        SUM ( Trans[Amount] ), 
        'calendar'[Date] <= CurrentDate,
        Trans[Serial] <= CurrentSerial,
        VALUES ( 'calendar'[Year] )
    )
RETURN
    Result

[tamerj1]

Hi @Pbi07 

have you tried this solution?

[Pbi07]

@tamerj1 

Ran in to the issue when the serails are not in order, meaning if i had a serial with lower # the total comes out wrong.. 

[tamerj1 · Accepted solution]

@Pbi07 

Then you have to have an index or ranking column. In other words, for each date-serial combination there must be a unique sort order number column. There a method to do that using dax it is preferable to be done using power query. 

[Pbi07]

Thanks @tamerj1 . Let me try this out how it behaves with duplicate serials