Last YTD DAX calculation by Fiscal Year - Leap Year

[JamesHinton]

Hi,

Do the YTD functions deal with leap years where the Fiscal Year End Date is end February ?  The parameter only accepts a constant string value and does not seem to deal with the issue where comparative years are leap years - e.g "02/29" and "02/28" - 2016/02/29 cf. 2017/02/28?

Is it possible to use these patterns in this case?

Thanks in advance.

James Hinton.

[OwenAuger · Accepted solution]

Hi James,

Unfortunately, the built-in YTD time intelligence functions don't work too well if your Fiscal Year End Date is the last day of February.

This is the behaviour I observed after playing around with DATESYTD/TOTALYTD, which makes Feb problematic:

1. If year_end_date = "02/28", "Feb-28", "2016/02/28"
   then actual year_end_date = 28-Feb in all years
2. If year_end_date = "02/29", "Feb-29" (no year specified)
   then actual year_end_date = 1-Feb in all years (weird, I know!)
3. If year_end_date = "2016/02/29" (or similar)
   then actual year_end_date = 29-Feb in leap years & 1-Mar in non-leap years

 
So you will need to write custom YTD measures instead 🙂

A good description of these is here on @MattAllington's blog:
http://exceleratorbi.com.au/dax-time-intelligence-beginners/

Some examples of how you could write a YTD measure below, assuming your fact table is related to a Date table's Date column.

1. If you add a Fiscal Year column to your 'Date' table, this measure would work:

YourMeasure YTD =
CALCULATE (
    [YourMeasure],
    FILTER (
        ALL ( 'Date' ),
        AND (
            'Date'[Date] <= MAX ( 'Date'[Date] ),
            'Date'[Fiscal Year] = MAX ( 'Date'[Fiscal Year] )
        )
    )
)

2. However, this longer-winded measure using variables & DATESBETWEEN seems to perform better.

YourMeasure YTD Better-performing =
VAR YearStartMonth = 3
VAR YearStartDay = 1
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR MaxYear =
    YEAR ( MaxDate )
VAR YearStartDateThisYear =
    DATE ( MaxYear, YearStartMonth, YearStartDay )
VAR YearStartDateLastYear =
    DATE ( MaxYear - 1, YearStartMonth, YearStartDay )
VAR YearStartDateSelected =
    IF (
        YearStartDateThisYear <= MaxDate,
        YearStartDateThisYear,
        YearStartDateLastYear
    )
RETURN
    CALCULATE (
        [YourMeasure],
        DATESBETWEEN ( 'Date'[Date], YearStartDateSelected, MaxDate )
    )

Once you have written YTD measures, you can easily write YTD Last Year measures such as:

YourMeasure YTD Last Year =
CALCULATE ( 
    [YourMeasure YTD],
    SAMEPERIODLASTYEAR( 'Date'[Date] )
)

Thankfully SAMEPERIODLASTYEAR and DATEADD are smart enough to translate a complete month of Feb in one year to the complete month of Feb in the previous year, regardless of whether either year is a leap year.

Anyway, have a play around,
Owen 🙂

[johanjsc]

Thanks - this solved my Leap Year issue with FYTD quickly!

[JamesHinton]

Thank you Owen.  Your detailed reply is most helpful and much appreciated.

Regards,

James