FTE calculation using working days for different time horizons

[Anonymous]

Hello all,

I was searching if there was something similar but did not find something for all the requirements I have.

Explaining the issue:

Data available
- I have one table with a list of activities, start date, end date, corresponding FTEs, among others

Example:

Activity	Start Date	End Date	FTEs
Activity 1	15 Dec 2020	19 Jun 2021	50

The FTEs column corresponds to the FTEs for the whole duration of the Activity

- I have a separate date table with all the days, having a column mentioning if it is a weekend or not

Desired output

- I need a way to calculate the monthly, yearly (even daily) values of FTEs. For this, I should consider that the number of FTEs should be divided by time according to working days, not calendar days (ignore holidays, consider weekdays only).

Example: if I want to look at a month view, I would have for the activity presented as below

Month	FTEs
Dec 2020	28.26*
Jan 2021	50.00
Feb 2021	50.00
Mar 2021	50.00
Apr 2021	50.00
May 2021	50.00
Jun 2021	31.82*

*1 Value adjusted via working days

[Working days for the activity in Dec 2020 (13) / Working days for Dec 2020 (23)]*FTEs (50) = 28.26

*2 same logic as *1, but considering the interval between 01 Jun 2021 and 19 Jun 2021: (14/22)*50 = 31.82

Basically, each time the date interval intersects the end or start of the activity, the way we do the calculation changes slightly. It always considers the ratio of Working days of the activity vs the number of working days of the interval in analysis.

If I look at this same activity from a year perspective, I should have:

Year	FTEs
2020	2.48
2021	23.18

But image we were talking about the same activity but with the dates 15 Dec 2020 to 19 Jun 2022. In this case, it would be:

Year	FTEs
2020	2.48
2021	50
2022	23.08

This is the same logic as before, but for the year instead of the months.

What has been done so far
Consider we have the following activities:

The monthly view should be something like:

BUT showing all the months anyway.

The measure I created seemed to be working for some limited number of rows – then I start having some memory issues and I could not totally compare if the actuals would work properly.

Measure =
VAR _FirstDate =
    FIRSTDATE ( 'Date'[Date] )
VAR _LastDate =
    LASTDATE ( 'Date'[Date] )
RETURN
    SUMX (
        FACTS,
        CALCULATE (
            SUM ( FACTS[Result gross (FTE)] )
                * DIVIDE (
                    CALCULATE (
                        COUNTROWS ( 'Date' ),
                        DATESBETWEEN (
                            'Date'[Date],
                            SUM ( FACTS[Start date] ),
                            SUM ( FACTS[Finish date] )
                        ),
                        'Date'[Weekend] = FALSE ()
                    ),
                    CALCULATE (
                        COUNTROWS ( 'Date' ),
                        DATESBETWEEN ( 'Date'[Date], _FirstDate, _LastDate ),
                        'Date'[Weekend] = FALSE ()
                    ),
                    0
                ),
            FACTS[Start date] <= _LastDate,
            FACTS[Finish date] >= _FirstDate
        )
    )

Help would be much appreciated.

Thank you all for your help,

Pedro

[daxer-almighty · Accepted solution]

@Anonymous ,

Here's a version that does not use CALCULATE and should be MUCH faster. Have a good look at its mechanics and if it does not work on the first attempt, do not panic, just try to understand how it works and adjust it accordingly. But beware of CALCULATE that's executed row by row on a fact table. YOU SHOULD NEVER DO IT AS IT'LL ALWAYS KILL PERFORMANCE.

Total FTEs =
// Dates must NOT be connected to Facts
// and must be marked as a Date table
VAR __firstDate = MIN( 'Dates'[Date] )
VAR _lastDate = MAX( 'Dates'[Date] )
// assuming that WorkingDay is 1 for
// a working day and 0 for a weekend
var __workingDayCount = SUM( 'Dates'[WorkingDay] )
RETURN
if( __workingDayCount > 0,
    SUMX(
    
        FILTER(
            FACTS,
            // getting only the rows where
            // there is a non-empty overlap
            // between (start, end) and
            // (firstDate, lastDate)
            Facts[Start date] <= __lastDate
            &&
            Facts[Finish date] >= __firstDate
        ),
        
        // for each of the above rows calculate
        // the percentage of FTE
        var __fte = FACTS[Result gross (FTE)]
        var __lowerDate =
            MAX(
                __firstDate,
                Facts[Start date]
            )
        var __upperDate =
            MIN(
                __lastDate,
                Facts[Finish date]
            )            
        var __activityWorkingDayCount =
            SUMX(
                filter(
                    // We don't have to use
                    // ALL( Dates ) here due
                    // to the nature of the
                    // problem.
                    Dates,
                    and(
                        __lowerDate <= Dates[Date],
                        Dates[Date] <= __upperDate
                    )
                ),
                 Dates[WorkingDay]
            )
        return
            // do not use DIVIDE here as it
            // does nothing more than what
            // I've put in here and in fact
            // it slows down calculations
            __fte * __activityWorkingDayCount
                / __workingDayCount
    )
)

[Anonymous]

Hi,

Thank you for the great solution! I have tried to apply it for headcount measure but when for the each employee it counts it well, total number is super weird.

[Anonymous]

Thanks a lot @daxer-almighty ! Worked perfectly! It would be better if it is even faster but I guess with this amount of data it is not easy to handle 😋

Thanks for all the inputs!

[daxer-almighty]

OK. Tell me how fast it is, how many rows you calculate this for, maybe show the model (as this is of extreme importance) and your expected duration. It's a pity that we have to iterate over the fact table. Maybe, if we could change the model, the calculation would be much faster. It's not rare that a proper change in the model results in blazingly fast DAX...

[Anonymous]

Hello @daxer-almighty ,

I though I had answered, sorry for the delay.

So basically, concerning the timings, it is a little bit slow for some visuals. The ones presented below are going at the month level from 1 Jan 2020 to 30 Jun 2022.

If I try to drill down (at day level) - not a must have but a nice to have - it obviously takes much more time.

I am not able to share the pbix directly because of the data it contains. The times I sent are for a "simplified" version of the DM (only three tables).

Not sure if it helps but please consider:

- 1 date Table

Date = ADDCOLUMNS(CALENDAR(DATE(2000,1,1),DATE(2100,12,31)),
"Year",YEAR([Date]),
"Month",MONTH([Date]),
"Day",DAY([Date]),
"Workday",SWITCH(WEEKDAY([Date]),1,0,7,0,1))

- 1 fact Table

• with 3 756 516 rows
• 11 columns
  • Value Type = RANDBETWEEN(1, 2)

    Base = RANDBETWEEN(1, 3)

    Activity ONB = RANDBETWEEN(1, 17611)

    Proj Code = RANDBETWEEN(1, 279)

    Package ID = RANDBETWEEN(1, 427)

    Value = RANDBETWEEN(1, 50)

    Soft ID = RANDBETWEEN(1, 9868)

    Forecast ONB = RANDBETWEEN(1, 872166)

    From = RANDBETWEEN(TODAY()-3650, Today())

    To = RANDBETWEEN(TODAY(), Today()+3650)

    Base + Forecast ONB = 'Fact'[Base] & 'Fact'[Forecast ONB]

- 1 Dimension Table

     Based on the FACT table

• Dimension = SUMMARIZE('Fact', 'Fact'[Base], 'Fact'[Forecast ONB])

     Then added:

• Base + Forecast ONB = 'Dimension'[Base] & 'Dimension'[Forecast ONB]
• Dept = RANDBETWEEN(1,12)
• Func = RANDBETWEEN(1,86)
• Role = RANDBETWEEN(1,124)

The two tables are connected via Base + Forecast ONB.

Measure is very similar to what you stated:

GrossFTEs = 
// Dates must NOT be connected to Facts
// and must be marked as a Date table
VAR __firstDate = MIN( 'Date'[Date] )
VAR __lastDate = MAX( 'Date'[Date] )
// assuming that WorkingDay is 1 for
// a working day and 0 for a weekend
var __workingDayCount = SUM( 'Date'[Workday] )
RETURN
if( __workingDayCount > 0,
    SUMX(
    
        FILTER(
            'Fact',
            // getting only the rows where
            // there is a non-empty overlap
            // between (start, end) and
            // (firstDate, lastDate)
            'Fact'[From] <= __lastDate
            &&
            'Fact'[To] >= __firstDate
            &&
            'Fact'[Value Type] = "2"
        ),
        
        // for each of the above rows calculate
        // the percentage of FTE
        var __ftegross = 'Fact'[Value]
        var __lowerDate =
            MAX(
                __firstDate,
                'Fact'[From]
            )
        var __upperDate =
            MIN(
                __lastDate,
                'Fact'[To]
            )            
        var __activityWorkingDayCount =
            SUMX(
                filter(
                    // We don't have to use
                    // ALL( Dates ) here due
                    // to the nature of the
                    // problem.
                    'Date',
                    and(
                        __lowerDate <= 'Date'[Date],
                        'Date'[Date] <= __upperDate
                    )
                ),
                 'Date'[Workday]
            )
        return
            // do not use DIVIDE here as it
            // does nothing more than what
            // I've put in here and in fact
            // it slows down calculations
            __ftegross * __activityWorkingDayCount
                / __workingDayCount
    )
)

Sorry if it is a complex approach for a simple thing but as I cannot share directly the pbix, though it can be the best way to test this.

In this random DM, the times it take for similar visuals are (but take into consideration that, has the department, function and role are random, they have similar amounts of rows):

The highlighted graphs are filteres for each department.

The two graphs that are still loading are like that for a long time. They basically have the whole data available but from a "real life" perspective, it would never be used.

The page is filtered frol 1 Jan 2020 until 24 Dec 2023.

The Value type is only filtered for Base 1.

The Value type considered in filtered directly into the measure.

Thanks for your help,

Pedro

[v-lili6-msft]

hi @Anonymous 

For your case, you need to create a measure as below:

Total Amount = 
VAR tmpCalendar = ADDCOLUMNS('Date',"MonthYear",VALUE(YEAR([Date]) & FORMAT(MONTH([Date]),"0#")))
VAR tmpBilling = ADDCOLUMNS('Table',"MonthYearBegin",VALUE(YEAR([Start Date]) & FORMAT(MONTH([Start Date]),"0#")),
                                      "MonthYearEnd",VALUE(YEAR([End Date]) & FORMAT(MONTH([End Date]),"0#")))
VAR tmpTable = 
ADDCOLUMNS(
SELECTCOLUMNS(
    FILTER(
        GENERATE(
            tmpBilling,
            SUMMARIZE(tmpCalendar,[Year],[Month],[MonthYear],"Totalworkingdays",COUNTROWS(FILTER('Date','Date'[Weekend]=FALSE()&&'Date'[Year]=[Year]&&'Date'[Month]=[Month])))
        ),
        [MonthYear] >= [MonthYearBegin] &&
        [MonthYear] <= [MonthYearEnd]
    ),
    "Activity",[Activity],
    "Year",[Year],
    "Month",[Month],
    "FTEs",[FTEs],
    "Totalworkingdays",[Totalworkingdays],
    "Start",IF(DATE([Year],[Month],1)<[Start Date],[Start Date],DATE([Year],[Month],1)),
    "End",IF(DATE([Year],[Month]+1,1)-1>[End Date],[End Date],DATE([Year],[Month]+1,1)-1)
),
"Rate",DIVIDE(COUNTROWS(FILTER('Date','Date'[Weekend]=FALSE()&&'Date'[Year]=[Year]&&'Date'[Month]=[Month]&&'Date'[Date]>=[Start]&&'Date'[Date]<=[End])),[Totalworkingdays]))
return
SUMX(tmpTable,[FTEs]*[Rate])

Result:

and here is a similar post for you refer to:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

Regards,

Lin

[Anonymous]

Hello @v-lili6-msft ,

Thanks a lot for your input, quite interesting approach.

Although it worked, I faced some performance issues with the measure. I came up with a different one - that does also have performance issues - but the totals seems ok here. As it is a different aim (only improving the performance), I have posted it into a different link.

Anyway, here is the measure and the expected results (also present in the other link):

Total FTEs = 
VAR _FirstDate =
    FIRSTDATE ( 'DateC'[Date] )
VAR _LastDate =
    LASTDATE ( 'DateC'[Date] )

RETURN
    SUMX(FACTS, CALCULATE (
        SUM ( FACTS[Result gross (FTE)] )
            * DIVIDE (
                CALCULATE (
                    SUM ( 'DateC'[WorkingDay] ),
                    DATESBETWEEN ( 'DateC'[Date], MAx(_FirstDate,SUM(FACTS[Start date])), MIN(_LastDate,SUM(FACTS[Finish date]) ))
                ),
                Calculate(
                    SUM ( 'DateC'[WorkingDay] ),
                    DATESBETWEEN ( 'DateC'[Date], _FirstDate, _LastDate )
                ),
                0
            ),
        FACTS[Start date] <= _LastDate,
        FACTS[Finish date] >= _FirstDate
    ))

Thanks a lot for your help,

Pedro