Forum Discussion
Last Year Comparisons for Non-Standard Calendars
I am working with a Retail 4-4-5 Fiscal Calendar where the fiscal year doesn't end on Dec 31st. Because of this, standard DAX functions like SAMEPERIODLASTYEAR return the wrong dates. How can I calculate Last Year's Sales based on a custom 'Fiscal Week' or 'Fiscal Day' index?
When standard date functions fail, the most robust way to handle "Previous Year" logic is to use a Custom Index in your Date Table.
1. The Setup (Power Query or Calculated Column)
In your Date Table, ensure every date has a Relative Day Index. This is a continuous number where today is 0, yesterday is -1, and so on.
However, for Fiscal Year comparisons, the best approach is to use a Fiscal Day of Year column (1 through 364/365).
2. The DAX Measure
Instead of using Time Intelligence, use FILTER and ALL to shift the context manually:Total Sales LY (Custom Fiscal) =
VAR CurrentFiscalYear = SELECTEDVALUE('Date'[FiscalYear])
VAR CurrentFiscalDay = SELECTEDVALUE('Date'[FiscalDayOfYear])RETURN
CALCULATE([Total Sales], ALL('Date'), 'Date'[FiscalYear] = CurrentFiscalYear - 1,
'Date'[FiscalDayOfYear] = CurrentFiscalDay)
1 Reply
When standard date functions fail, the most robust way to handle "Previous Year" logic is to use a Custom Index in your Date Table.
1. The Setup (Power Query or Calculated Column)
In your Date Table, ensure every date has a Relative Day Index. This is a continuous number where today is 0, yesterday is -1, and so on.
However, for Fiscal Year comparisons, the best approach is to use a Fiscal Day of Year column (1 through 364/365).
2. The DAX Measure
Instead of using Time Intelligence, use FILTER and ALL to shift the context manually:Total Sales LY (Custom Fiscal) =
VAR CurrentFiscalYear = SELECTEDVALUE('Date'[FiscalYear])
VAR CurrentFiscalDay = SELECTEDVALUE('Date'[FiscalDayOfYear])RETURN
CALCULATE([Total Sales], ALL('Date'), 'Date'[FiscalYear] = CurrentFiscalYear - 1,
'Date'[FiscalDayOfYear] = CurrentFiscalDay)