Count of Open\Current Records by half year

Question

I have a calendar table and two other tables, of records A and records B. The calendar date has an active relationship with records A table and an inactive relationship with records B table. Relation type one to many as below.

What I'd like to calculate for the records B table is a measure to display a count of the number of open\current records when I select a half year from the slicer. It should calculate the open records from the previous half years and the current ones.

The slicer's field is Half Year text format (which is the Half Year when the records were created), but the relationships with the calendar table is based on creation records date - a date data type). More important, I have only one date column in the Records B table, which is the creation date to calculate the measure and no end/close date. That is why I find tricky to calculate the measure.

I've tried the following solutions:

I've followed this link:

https://community.fabric.microsoft.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/bc-p/2957695

In particular, modifying this formula:

Current Employees = CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=max('Date'[Date]) && (ISBLANK(Employee[End Date]) || Employee[End Date]>max('Date'[Date]))),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))

Instead of crossfilter I used USERELATIONSHIP as it has an inactive relationship with the calendar date and removed the code that mentioned employee[End Date].

I have also used the count('Records B', Filter('Records B'[Creation Date] <=SELECTEDVALUE('Calendar'[Date]))

But the < sign in both measures it looks like is ignored.

Any help is really appreciated.

Answer 1

I solved the calculations using this method applied to table B. Link: https://carldesouza.com/power-bi-dax-userelationship-multiple-dates/