Calculate the % of absenteeism with a table of people end date and leave date of the company

[Syndicate_Admin]

GOOD DAYS TO ALL. I need your help because I am not able to get the data

I have a fact table where I have the hired staff with a column that has the start date of the contract and the end date of the contract.

In this formula I count the people I have for each month

Nº Workers = CALCULATE(COUNTx(FILTER('dmart dim_person','dmart dim_person'[employment_start_date]<=max(dFechas[Fecha]) && (ISBLANK('dmart dim_person'[employment_end_date]) || 'dmart dim_person'[employment_end_date]>max(dDates[Date]))),('dmart dim_person'[person_id])),CROSSFILTER('dmart dim_person'[employment_start_date],dDates[Date],None))

And then I have another fact table where I have the same people with a column, start date of their medical leave, and the date of discharge.

Hired Employee = CALCULATE(COUNT('fPersonalBaja'[person_id]),USERELATIONSHIP('fPersonalBaja'[Inicio_baja],dFechas[Fecha]))

The problem I am having is that when I have a person who has been on sick leave, for example, since February 2024 and to date is still on leave, the table shows me the sick leave in February, but then I go to March and it does not appear.

What formula would you use to give me the result I want?, that is, that until I have a medical discharge date, this person continues to appear to me on the low.

Thanks a lot

Jose Luis

[TomMartens]

Hey,

Make sure that no login is required to download the sample file.
Unfortunately, I will be swamped until the upcoming weekend.

Regards,

Tom

[TomMartens]

Hey @Syndicate_Admin ,

please create a pbix file that contains sample data but still reflects your semantic model (talbes, relationships, calculated columns, and measures). Upload the pbix to OneDrive, Google Drive, or Dopbox and share the link. If you are using a psreadsheet to create the sample data instead of the manual import method, share the spreadsheet as well.

Describe the expected result based on the sample data you provide.

Regards,

Tom

[Syndicate_Admin]

Adjunto link one drive

Test

[Syndicate_Admin]

Good morning

I don't know if he's going to let you see it, because this One Drive is for companies and they don't let us share things to the outside. If you can't see it, please provide me with your email address and I'll email it to you.

Thanks a lot

Jose Luis

[Syndicate_Admin]

I've taken this to my one drive

https://drive.google.com/drive/folders/1rUJxqWGjJ7h-JwsqmzCT781XPxr0GIDm?usp=drive_link

[Syndicate_Admin]

Good afternoon

Have you been able to look at anything about the formulas I need?

Thanks a lot

Jose Luis