Date filter that crosses over years

[Binway]

Hi

Our Year starts on the 01/04 and I need to sum sales for the YTD eg currrently from April to August.

The calculation I am using is below that has a dynamic end Date.

YTD Amount = CALCULATE(SUM(Sales[Amount])
,DATESBETWEEN('Date'[Date],DATE(YEAR(TODAY()),4,1), EOMONTH(TODAY(),0))

I have created a calculation that works fine for now but as the Calendar Year moves to Next year it will fail.

I have done some investigation but couldn't easily find anything to suit - the DATESYTD function didn't seem appropriate.

Appreciate the help.

Binway

[Ashish_Mathur · Accepted solution]

Hi,

Try this

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

Ensure that there is a relationshiop from the Date column of the Sales Table to the Date column of the Date Table.  In the Date Table, use this calculated column formulas to extract the Year and month

Year=YEAR('Date'[Date])

Month=FORMAT('Date'[Date],"mmmm")

Create 2 slicers - one each for Year and Month.  Select any one year and month in the slicers.

Hope this helps.

[Binway]

Excellent thanks.

I thought the DATESYTD function would sum everything up til 31/3 next year but it seems it only sums up until TODAY().

Looks good.

[Ashish_Mathur]

You are welcome.