topic Re: DAX dynamic measure, non standard calendar in DAX Commands and Tips

DAX dynamic measure, non standard calendar

sm1driver — Thu, 13 Feb 2025 00:55:58 GMT

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 ?

Re: DAX dynamic measure, non standard calendar

bhanu_gautam — Thu, 13 Feb 2025 03:34:44 GMT

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
)
)
)
)

Re: DAX dynamic measure, non standard calendar

sm1driver — Thu, 13 Feb 2025 04:40:40 GMT

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!