Struggling with summarize, calculate, sumx to calculate Daily FTE calculation

[mlsx4]

Hello everyone,

I'm stuck at calculating FTE per employee in Power BI. Let me show you some examples of my tables.

Firstly, I have a table with all the information about the contract (number of hours per week, starting and ending date, etc. and also the center this employee belongs to).  Just to mention here, that an employee can appear several times with different kind of contracts.

On the other hand, I have the absences table

Additionally, I have another table with all the centers and companies of the group.

Then, my problem is that I need to calculate the daily FTE for each employee, taking into several things: the kind of contract which is active at the moment, if the employee is in the absences table, the center they belong... Imagine, we want to calculate it for May. Day 1 both will be active so I will have a ratio of 0.75+1=1.75. Day 2, Employee 2 is on holidays so it will be 0+1=1, and so on...

I have tried to do an approach by calculating the % regarding the contract first. And then having a kind of multiplier by 0 when an employee is on holidays, and another multiplier by 0 when an employee has finished the contract. The first part is working properly, but the multipliers aren't. These are my measures for holidays or absences:

And for the multiplier:

The problem is that BajasD never returns 1-> it adds all the leaves. Therefore, even if the employee isn't in the company for a day it will always multiplies by 1 because the condition will compare with the total.

I'm pretty sure it is because I need to filter or summarize something, but I don't know what I need to do. It is a really low granularity (company->center->year->month->day->employee).

Hope someone could help me.

Thank you in advance

[v-shex-msft]

Hi @mlsx4,

It seems like a common analysis requirement from date range that defined by multiple fields. You can look the following blog start date, end date part if it helps:

Before You Post, Read This: start, end date 

Regards,

Xiaoxin Sheng

[mlsx4 · Accepted solution]

UPDATE: I have just added a calculate in the multiplier and now, it's working perfectly

Thank you for your response, but start/end date is not what I'm really struggling with. 

The thing is that if I don't try to get a multiplier (which I need), BajasD is working for each day, for each worker. However, when I try to do the multiplier always aggregates the measure BajasD. So, if I have 6 workers on a leave because of an accident, instead of comparing one by one for this day, it will compare to 6. Therefore, it will never be 0.

An example.

Employee 1- > misses from 1st June to 4th of June

Employee 2-> misses from 2nd June to 3rd of June

Then if I try to make the multiplier:

Day 1: employee 1 must be 0, employee 2 must be 1

Day 2: employee 1 must be 0, employee 2 must be 0

However multiplier will return always 1 since it will count 2 (number of workers out).