Cumulative sum of a column, but renewing the calculation each March 30th.

[dalmofm]

Hello Guys,

I need to create a dax that calculates the cumulative sum of a column, but renewing the calculation each March 30th.

Date	Value	Cumulative Sum	Expected Result
2018-02-02	$ 120,00	???	$  120,00
2018-05-18	$ 135,00	???	$  135,00
2018-08-31	$ 135,00	???	$  270,00
2018-12-14	$ 135,00	???	$  405,00
2019-03-29	$ 135,00	???	$  540,00
2019-07-12	$ 150,00	???	$  150,00
2019-10-25	$ 150,00	???	$  300,00
2020-02-07	$ 150,00	???	$  450,00
2020-05-22	$ 165,00	???	$  165,00
2020-09-04	$ 165,00	???	$  330,00
2020-12-18	$ 165,00	???	$  495,00
2021-04-02	$ 175,00	???	$  175,00

[amitchandak]

@dalmofm , with help from date table, try

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"3/30"))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[dalmofm · Accepted solution]

Hello Sir,

The recommended formula worked but with a small adjust, as below:

YTD Sales = CALCULATE(

SUM ('Sales'[Sales]),

ALL ( 'Sales' ),

DATESYTD('Date'[Date],"30/03"))

Thank you so much for your great help !!!