Calculating FTE - events in progress

[HarmaW]

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:

I have read Gerhard's blog and tried to adapt the solution
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.

[v-yiruan-msft]

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