Year To Date values in DAX for fiscal year starting from March (ending in February)
The sum of values from the beginning of the year is generally quite simple in DAX. For calendar year, we will use something like:
- ytd calendar = TOTALYTD(
SUM(My_data[Value]),
'Dimension'[Date])
If it is a fiscal year that starts in January, February, April... i mean at any month except March, then just add another argument declaring the end of year:
- ytd fiscal year not from March = TOTALYTD(
SUM(My_data[Value]),
'Dimension'[Date],"5/31")
But what to do if the last day of the fiscal year falls at the end of February, which is one day longer in leap years?
For that, we have to use a more complicated formula. The original is from web, I´ve just added comments.
If you're just concerned with application and don't want to understand the formula, just replace the bolded parts with your own time dimension and your own measure.
- ytd fiscal year from March =
VAR YearStartMonth = 3 //first month of fiscal year
VAR YearStartDay = 1 //first day of fiscal year
VAR MaxDate =
MAX ( 'calendar dimension'[date] ) //last day of period
VAR MaxYear =
YEAR ( MaxDate ) //year of last day of period
VAR YearStartDateThisYear =
DATE ( MaxYear, YearStartMonth, YearStartDay ) //first day of calculated period
VAR YearStartDateLastYear =
DATE ( MaxYear - 1, YearStartMonth, YearStartDay ) //first day of calculated period last year
VAR YearStartDateSelected =
IF (
YearStartDateThisYear <= MaxDate, //if the period is month from March
YearStartDateThisYear, //then take the beginning of period this year
YearStartDateLastYear //otherwise take the beginning of period last year
)
RETURN
CALCULATE (
[my_measure],
DATESBETWEEN (
'calendar dimension'[date],
YearStartDateSelected,
MaxDate
)
)