cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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 @Anonymous ,

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.