Dynamic Formula (Measure) that would check whether a value exists in a selected previous period

[Anonymous]

I am tasked to create a report that will essentially map out the headcount changes from any chosen quarter (ex. Q3 vs Q2, Q2 vs Q1).

I need to separate the headcount walk to:

1) Decrease from employees that have left (in Q2 but not in Q3)

2) Increase from new employees (in Q3 but not in Q2)

3) Changes in existing employees (in both Q2 and Q3)

With the help of experts in this Power BI forum, I was able to get a formula for month-on-month comparisons, but realized that it works as a Calculated Column. Which means, if there are changes to the quarters chosen, the report does not adapt properly to what is being asked of it.

Is it possible to create a Measure that will?

1) Indicate whether a value 'New to Latest Quarter': value exists in the max quarter chosen compared to min quarter chosen

2) Indicate whether a value 'Not in Latest Quarter': value does not exist in the max quarter chosen compared to min quarter chosen

For additional context: I use a reference value that is a concatenate of Employee ID and Account Name.

If I'm able to filter using this 'New to Latest Quarter' and 'Not in Latest Quarter' measures, I think I'll be able to get the requested view.

Thanks in advance! Been stuck on this problem for a few weeks now.

[lbendlin]

Have you considered using INTERSECT() and EXCEPT() for this? Inside your measure you can construct table variables for the affected periods and then use the Venn functions as needed.