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 ( MaxYearYearStartMonthYearStartDay ) //first day of calculated period
    VAR YearStartDateLastYear =
        DATE ( MaxYear - 1YearStartMonthYearStartDay ) //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
            ) 
        )

Leave a Reply

Your email address will not be published.

*

clear formPost comment