If you have a fiscal year starting in March, you can have a problem using the time intelligence functions in DAX.
For example, by using February 28, the fiscal year starts on February 29th in leap years.
DATESYTD ( 'Date'[Date], "02-28" )
By using February 29 of a leap year, the fiscal year starts on March 1st in the leap years, but it starts on March 2nd in non-leap years.
DATESYTD ( 'Date'[Date], "2008-02-29" )
By using February 29 without the year, the fiscal year starts on February 2nd (yes, February!) in all the years.
DATESYTD ( 'Date'[Date], "02-29" )
I asked to Microsoft about this, but there is no valid fix for that.
The solution is to get rid of DATESYTD and write your own DAX code for that.
Here is a sample measure – copy the entire filter argument in your code. This is a clear example of a use case for custom table functions in DAX (or any other feature that would make it possible to reuse a filter in different measures).
Sales YTD 2 = CALCULATE ( [Sales Amount], VAR FirstFiscalMonth = 3 -- Set the first month of the fiscal year VAR LastDay = MAX ( 'Date'[Date] ) VAR LastMonth = MONTH ( LastDay ) VAR LastYear = YEAR ( LastDay ) - IF ( LastMonth < FirstFiscalMonth, 1 ) VAR FilterYtd = DATESBETWEEN ( 'Date'[Date], DATE ( LastYear, FirstFiscalMonth, 1 ), LastDay ) RETURN FilterYtd )
Returns a set of dates in the year up to the last date visible in the filter context.
DATESYTD ( <Dates> [, <YearEndDate>] )