Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

undefined

Hi,

 

Please can someone help me with this DAX measure. I am trying to get headcount in 13 rolling months such that when I select any months, it shows me the preceding 12 months. I have tried using most solutions online and still have not gotten an answer. this seen to be the only one that works on my measure  However, some of the values for the headcount arre wrong for some months. What do you think is wrong with the measure?

 

 

Headcount Rolling 13 Months :=
VAR MaxDate = MAX(Dates[Date])
VAR MaxDate13MonthsAgo = EOMONTH(MaxDate, -13)
VAR Result =
IF (
HASONEVALUE("Duplicate date table [YEARMonth])") &&
MAX('Duplicate date table'[Date]) <= MaxDate &&
MAX('Duplicate date table'[Date]) > MaxDate13MonthsAgo,
CALCULATE (
[Headcount],
FILTER (
ALL (Dates[FY Month], Dates[Financial Year], Dates[YEARMonth]),
Dates[FY Month] = VALUES('Duplicate date table'[FY Month]) &&
Dates[Financial Year] = VALUES('Duplicate date table'[Years])
)
),
BLANK()
)
RETURN Result