cancel
Showing results for
Did you mean:
New Member

## Calculating FTE - events in progress

I am attempting calculate the total FTE in a given reporting period, such as a month.

FTE=(worked days in reportingperiod)/(available days in reportingperiod)*availability
We use weekdays to calculate the FTE.

The worked days are implicit in an contract episode. The employee contract mostly has several episodes due to change of position/salary etc. All episodes have a start date. Most of the time the episodes have a fixed end date, but some have an open end date. To prevent extra scenarios I have replaced these open end dates with a high date (31-12-2022). Episodes may start or end at any given date. Availability may vary over episodes.

At the end the overview should look something like this:

https://blog.gbrueckl.at/2014/12/events-in-progress-for-time-periods-in-dax/

But In my case I have different calculations for the FTE needed per scenario.

Working with episodes and reporting periodes results into 6 scenarios, which makes the calculation of the total FTE complex. Below you can find an overview of the scenarios

Here is a link to a folder with the power-bi file and the .cvs with the data, to see the measures and the available data.

Tried but failed:

This works if 1 person is selected, but if multiple people are selected, it won’t work anymore because of the use of min/max at the EpisodeStartDate/EpisodeEndDate

working days all scenarios =

VAR StartReportingPeriod = MIN('10 Calendar'[Date])

VAR EndReportingPeriod = MAX('10 Calendar'[Date])

VAR StartEpisode = MIN('70 EmploymentHistory'[EpisodeStartDate])

VAR EndEpisode = MAX('70 EmploymentHistory'[EpisodeEndDate])

VAR Result1 =

SWITCH(

TRUE(),

StartEpisode<=StartReportingPeriod && EndEpisode<EndReportingPeriod && EndEpisode>StartReportingPeriod, (DATEDIFF(StartReportingPeriod,EndEpisode,DAY)+1),

StartEpisode<=StartReportingPeriod && EndReportingPeriod<=EndEpisode, (DATEDIFF(StartReportingPeriod,EndReportingPeriod,DAY)+1),

StartEpisode>StartReportingPeriod && EndEpisode<= EndReportingPeriod, (DATEDIFF(StartEpisode,EndEpisode,DAY)+1),

StartEpisode>StartReportingPeriod && EndEpisode>EndReportingPeriod && StartEpisode<=EndReportingPeriod, (DATEDIFF(StartEpisode,EndReportingPeriod,DAY)+1))

Return

Result1

This works if 1 person is selected, but if multiple people are selected, it won’t work anymore because of the use of min/max at the EpisodeStartDate/EpisodeEndDate

Another try:

I looked at events in progress online and encountered a lot of solutions. You can calculate per scenario, but I encountered the problem that I can only use measures for my calculation and not columns. And that is not calculating the amount of days.

scenario 2 =

CALCULATE(

SUM('70 EmploymentHistory'[Availability]),

FILTER(VALUES('70 EmploymentHistory'[EpisodeStartDate]),'70 EmploymentHistory'[EpisodeStartDate]<MIN('10 Calendar'[Date])),

FILTER(VALUES('70 EmploymentHistory'[EpisodeEndDate]),'70 EmploymentHistory'[EpisodeEndDate]>=MAX('10 Calendar'[Date]))

)

It would be great if someone could help me solve this issue.

Community Support

Hi @HarmaW ,

You can create a calculated column as below in table [70 EmploymentHistory]:

``````Workdays for per person =
VAR StartReportingPeriod =
MIN ( '10 Calendar'[Date] )
VAR EndReportingPeriod =
MAX ( '10 Calendar'[Date] )
VAR _countofwkdays =
CALCULATE (
COUNTROWS ( '10 Calendar' ),
FILTER ( '10 Calendar', '10 Calendar'[WeekdayWeekend] = "Weekday" ),
DATESBETWEEN (
'10 Calendar'[Date],
'70 EmploymentHistory'[EpisodeStartDate],
'70 EmploymentHistory'[EpisodeEndDate]
)
)
RETURN
DIVIDE (
_countofwkdays,
DATEDIFF ( StartReportingPeriod, EndReportingPeriod, DAY ) + 1
) * '70 EmploymentHistory'[Availability]``````

If the above one is not what you want, please explain more details of your expected result with some examples. Thank you.

Best Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors