Forum Discussion
Advocate ILast Year Comparisons for Non-Standard Calendars
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)
Super UserWhen 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)