topic Re: Cummulative sum not working properly (it is adding for previous months also ) in DAX Commands and Tips

Cummulative sum not working properly (it is adding for previous months also )

Samarth-Borade — Mon, 23 Jan 2023 07:34:27 GMT

Code: cum_tot = CALCULATE ([sum_trip_cost],FILTER(ALL(ff[Trip Date]),ff[Trip Date] < MAX (ff[Trip Date] )))

It is also adding value for previous month , even in slicer if i select a particular month it still adds last all month's values.

What i want : if i select 'September' Month in slicer , it will only add in september .

Re: Cummulative sum not working properly (it is adding for previous months also )

FreemanZ — Mon, 23 Jan 2023 08:36:18 GMT

hi Samarth-Borade

try like:

cum_tot = CALCULATE ( [sum_trip_cost], FILTER( ALL(ff[Trip Date]), ff[Trip Date] < MAX (ff[Trip Date] ) && YEAR( ff[Trip Date]) = YEAR( MAX (ff[Trip Date] )) && MONTH( ff[Trip Date]) = MONTH( MAX (ff[Trip Date] )) ) )

Re: Cummulative sum not working properly (it is adding for previous months also )

AilleryO — Mon, 23 Jan 2023 09:55:31 GMT

Hi,

If you want a running sum only within the month, wouldn't it be easier to use TOTALMTD() function ?

I do not know about the details of your project, but it seems to be the easiest way...

The formula you used is perfect when you need a running sum on all lines, but for monthly running sum the best is to use TOTALMTD or for quarter TOTALQTD...

Let us know if it suits your needs

Let us know