DAX dynamic measure, non standard calendar

[sm1driver]

Hi experts,

I only have a non-standard calendar dimension because the granularity is week level fact data.

I tried creating a Previous year measure using the following

RevenuePrevYear =

VAR CurrentYear = MAX('Calendar Fiscal'[FiscalYear])

VAR CurrentWeek = MAX('Calendar Fiscal'[FiscalWeek])

RETURN

CALCULATE(

_Measures[Revenue],

FILTER(

ALL('Calendar Fiscal'),

'Calendar Fiscal'[FiscalYear] = (CurrentYear - 1)&& --2024 minus 1 = 2023

'Calendar Fiscal'[FiscalWeek] = CurrentWeek- 100 --202452 minus 100 = 202352

)

)

This only seems to work when the rows are sliced by FiscalWeek. shows incorrect values when view by fiscal months

Is there a way for it to dynamically change when sliced by fiscal month or week and so on ?

[bhanu_gautam · Accepted solution]

@sm1driver , Try using

DAX
RevenuePrevYear =
VAR CurrentYear = MAX('Calendar Fiscal'[FiscalYear])
VAR CurrentWeek = MAX('Calendar Fiscal'[FiscalWeek])
VAR CurrentMonth = MAX('Calendar Fiscal'[FiscalMonth])
RETURN
IF(
ISINSCOPE('Calendar Fiscal'[FiscalWeek]),
CALCULATE(
_Measures[Revenue],
FILTER(
ALL('Calendar Fiscal'),
'Calendar Fiscal'[FiscalYear] = (CurrentYear - 1) &&
'Calendar Fiscal'[FiscalWeek] = CurrentWeek
)
),
IF(
ISINSCOPE('Calendar Fiscal'[FiscalMonth]),
CALCULATE(
_Measures[Revenue],
FILTER(
ALL('Calendar Fiscal'),
'Calendar Fiscal'[FiscalYear] = (CurrentYear - 1) &&
'Calendar Fiscal'[FiscalMonth] = CurrentMonth
)
)
)
)

[sm1driver]

Magic! 
didn't know of ISINSCOPE keyword. 
awesome. 
obviously added the -100 in the filter for the previous year in weeks and months and it worked beautiful. appreciate it, thank you!