Forum Discussion
Solved
Please help with CALCULATE and FILTER dax function
I know the way I am trying to solve this is not elegant at all, but I do have a simple table called "HR Data" and a measure define like this:
Headcount Employees = CALCULATE(DISTINCTCOUNT('HR Data'[Employee Id])) + 0
I am now trying to create another measure called "Headcount Employees one year before" where I would like to have the number of headcounts one year before. Therefore if we are in "Quarter 1 23/24" I want the headcounts calculated for "Quarter 1 22/23".
Headcount Employees one year before =
SWITCH(
SELECTEDVALUE('HR Data'[Quarter-Year]),
"Quarter 1 23/24",CALCULATE(DISTINCTCOUNT('HR Data'[Employee Id]), FILTER('HR Data','HR Data'[Quarter-Year] = "Quarter 1 22/23")),
"Quarter 2 23/24",CALCULATE(DISTINCTCOUNT('HR Data'[Employee Id]), FILTER('HR Data','HR Data'[Quarter-Year] = "Quarter 2 22/23")),
"Quarter 3 23/24",CALCULATE(DISTINCTCOUNT('HR Data'[Employee Id]), FILTER('HR Data','HR Data'[Quarter-Year] = "Quarter 3 22/23")),
"Quarter 4 23/24",CALCULATE(DISTINCTCOUNT('HR Data'[Employee Id]), FILTER('HR Data','HR Data'[Quarter-Year] = "Quarter 4 22/23")),
"0")
I don't have any date structure, and I know this is not best practice, but can you please let me know how to do it ?
Thanks
A.
Heacount Employees One Year Before = CALCULATE(sum('HR Data'[Headcount Employees]),OFFSET(-4,allselected('HR Data'),MATCHBY('HR Data'[Index])))
6 Replies
- lbendlin
Super User
Headcount Employees one year before = CALCULATE(DISTINCTCOUNT('HR Data'[Employee Id]), SAMEPERIODLASTYEAR(Calendar[Date]))(if you don't have a Calendar table then you should add one).
Thank you for the reply. I totally agree this should the right way of solving this, however I just need to build a quick proof of concept plus I am using bespoken quarters so a bit tricky to solve in that way.
I wouldn't suggest the solution I am trying to implement to anyone, but is there a simple way to fix my DAX instead ?
Thanks
A.
