Please help with CALCULATE and FILTER dax function

[aabati68]

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.

[lbendlin]

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

[aabati68]

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.

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[aabati68]

Thanks, this is the expected outcome:

[lbendlin · Accepted solution]

Heacount Employees One Year Before = CALCULATE(sum('HR Data'[Headcount Employees]),OFFSET(-4,allselected('HR Data'),MATCHBY('HR Data'[Index])))

[aabati68]

Wonderful, works really well, thanks a lot !

A.