topic Running sum formula that does NOT reset at every year but starts with some constant amount. in DAX Commands and Tips

Running sum formula that does NOT reset at every year but starts with some constant amount.

AVGUser_PBI — Tue, 12 Dec 2023 22:38:46 GMT

I am working on to get running sum for cost that doesn't reset on year. I got one formula working but, for some reason it doesn't start with correct number as shown below:

there are no cost before 2017 but running sum line starts from 2014 with some constant cost. please help me figure out what I am doing wrong?

DAX:

Project_cost_cum_year = CALCULATE([project_cost_total],

FILTER(ALLEXCEPT(effective_date, effective_date[Date]),

ISONORAFTER(effective_date[Date].[Year], MAX(effective_date[Date].[Year]),DESC)

)

thanks,

Re: Running sum formula that does NOT reset at every year but starts with some constant amount.

AVGUser_PBI — Tue, 12 Dec 2023 22:57:40 GMT

on the side note:

If change the formula as shown below, then the chart starts with correct year and amount, however, it doesn't do the running sum.

Re: Running sum formula that does NOT reset at every year but starts with some constant amount.

sevenhills — Wed, 13 Dec 2023 01:03:22 GMT

Just curious, can you try and see if this works ...

CALCULATE([project_cost_total], FILTER(ALLSELECTED(effective_date), effective_date[Date] <= MAX(effective_date[Date]) ) )

CALCULATE([project_cost_total], FILTER(ALL(effective_date), effective_date[Date] <= MAX(effective_date[Date]) ) )

Re: Running sum formula that does NOT reset at every year but starts with some constant amount.

AVGUser_PBI — Wed, 13 Dec 2023 14:54:32 GMT

yes, AllSELECTED formula works! Also, I made a booboo with date format. (facepalm)

thank you.