Showing results for 
Search instead 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:


I have read Gerhard's blog and tried to adapt the solution


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 =



        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))





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 =


       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
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 =
        COUNTROWS ( '10 Calendar' ),
        FILTER ( '10 Calendar', '10 Calendar'[WeekdayWeekend] = "Weekday" ),
            '10 Calendar'[Date],
            '70 EmploymentHistory'[EpisodeStartDate],
            '70 EmploymentHistory'[EpisodeEndDate]
    DIVIDE (
        DATEDIFF ( StartReportingPeriod, EndReportingPeriod, DAY ) + 1
    ) * '70 EmploymentHistory'[Availability]

Calculating FTE - events in progress.JPG

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

Best Regards


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.

Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

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

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

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!

MPPC 2023 PBI Carousel

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