Handling multiple date relationships in measure

[kkouvaras]

I am attempting to build a matrix that maps out a historical count of active employees as of the first of each month in the past. 

I have a standard date table and an employee table, with the employee table having dates for Hire Date, Termination Date, ReHire Date and Previous Termination Date. 

What I am struggling with is building the logic into a measure that only allows an employee to be counted if they are "active" in the given month shown on the column, meaning their HireDate <= ColumnHeader (example: 4/1/2022) and their TerminationDate > 4/1/2022 (or Null). Eventually I also need to incorporate situations where someone was rehired/terminated again, but that is likely not hard to incorporate once I get something baseline working.

Since i'm dealing with multiple date relationships between my employee table and my date table, i've got all 4 built but only the HireDate one is active. I thought maybe I could apply a Filter with a UseRelationship modifier but I can only apply those to the Calculate and not the Filter. 

I feel like i'm missing something conceptually in determining how the measure understands the context it's in as far as which date to compare the Hire/Term dates to. Any suggestions? Thanks!

[Anonymous]

Hi @kkouvaras,

I'd like to suggest you take a look at the following link start date/end date apart to know more about the analysis of multiple date ranges:

Before You Post, Read This - Microsoft Power BI Community

Spoiler

You have a “start date” and an “end date” and are trying to know how many of something you have on all of the dates between your date intervals.

Take a look at these two Quick Measures as probably want something like them.

• Open Tickets
• Periodic Billing

Regards,

Xiaoxin Sheng

[DataInsights]

@kkouvaras,

It sounds as if the solution below would be a good option. The concept is to create a clone of the date table, and use DAX to control the filtering within the visual.

https://www.sqlbi.com/articles/show-previous-6-months-of-data-from-single-slicer-selection/