Forum Discussion

aabati68's avatar
aabati68
Frequent Visitor
2 years ago
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'...
  • lbendlin's avatar
    lbendlin
    2 years ago

    Heacount Employees One Year Before = CALCULATE(sum('HR Data'[Headcount Employees]),OFFSET(-4,allselected('HR Data'),MATCHBY('HR Data'[Index])))
aabati68's avatar
aabati68
Frequent Visitor
2 years ago

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's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

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

  • aabati68's avatar
    aabati68
    Frequent Visitor
    2 years ago

    Thanks, this is the expected outcome:

     

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      2 years ago

      Heacount Employees One Year Before = CALCULATE(sum('HR Data'[Headcount Employees]),OFFSET(-4,allselected('HR Data'),MATCHBY('HR Data'[Index])))
      • aabati68's avatar
        aabati68
        Frequent Visitor
        2 years ago

        Wonderful, works really well, thanks a lot !

        A.