DAX - Finding Previous Year Equivalent, based on Fiscal Calendar retaining time/row hierarchy

Question

I have 2 tables that have a many to one relationship defined by a 'FiscalWeekEndDate' value, like so;

WordsTable

FiscalWeekEndDate  | WordGroup   | IndexedVolume
01/01/2017         | Dining      | 1,000
01/01/2017         | Shopping    | 2,000
08/01/2017         | Dining      | 2,000
08/01/2017         | Sports      | 5,000

FiscalDatesTable

FiscalWeekEndDate | FiscalWeek | FiscalMonth | FiscalQuarter | FiscalYear
01/01/2017        | 21         | 5           | 2             | 2017
08/01/2017        | 22         | 5           | 2             | 2017

I'm trying to create a simple PY equivalent measure in DAX (previous year), that works at all levels of time hierarchy - i.e. PY for chosen fiscal week, month, quarter or year. I don't want a YTD (year to date) measure.

This is what I have so far;

Previous Year Indexed Volume:=
CALCULATE([IndexedVolume], 
FILTER(ALL('FiscalDatesTable'),'FiscalWeeksTable'[FiscalWeek]= 
MAX('FiscalDatesTable'[FiscalWeek]) && 'FiscalDatesTable'[FiscalYear]  = 
MAX('FiscalDatesTable'[FiscalYear]) - 1))

Which is returning this type of result;

Fiscal Year 2017: 7,000
Fiscal Quarter 2: 7,000
Fiscal Month 5: 7,000
Fiscal Week 22: 7,000
Fiscal Week 21: 3,000

Basically, the Max Fiscal Week value (22: 7,000) is returned for the Month, Quarter or Year dimension. I understand that's a problem due to my DAX Filter, but unsure what else to try.

The desired result would give me the correct Previous Year IndexedVolume value, regardless of the time dimension chosen. i.e. like below;

Fiscal Year 2017: 10,000
Fiscal Quarter 2: 10,000
Fiscal Month 5: 10,000
Fiscal Week 22: 7,000
Fiscal Week 21: 3,000

Phil Barnes Phil Barnes · Accepted Answer · 2017-04-30T21:41:29

A few hours of research and I finally cracked it by introducing a 'PreviousFiscalWeekEndDate' into the logic.

Here is the approach if anyone finds it useful.

Previous Year Indexed Volume:=
CALCULATE([IndexedVolume],
FILTER (
    ALL ( 'FiscalDatesTable' ),
    CONTAINS (
        VALUES ( 'FiscalDatesTable'[PreviousYearFiscalWeekEndDate] ),
        'FiscalDatesTable'[PreviousYearFiscalWeekEndDate],
        'FiscalDatesTable'[FiscalWeekEndDate]
    )
  )
)

DAX - Finding Previous Year Equivalent, based on Fiscal Calendar retaining time/row hierarchy

1 Answers