Forum Discussion

Anonymous

Not applicable

5 years ago

Solved

FTE calculation using working days for different time horizons

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

Need Help

daxer-almighty

5 years ago

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

7 Replies

daxer-almighty

Solution Sage

5 years ago

Anonymous ,

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
Not applicable
5 years ago
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
  Solution Sage
  5 years ago
  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
Not applicable
2 years ago
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.

v-lili6-msft

Community Support

5 years ago

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

FTE calculation using working days for different time horizons.pbix317 KB

Anonymous

Not applicable

5 years ago

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

Forum Discussion

FTE calculation using working days for different time horizons

7 Replies

Related content

Aling objets Vertical/Horizonal

Dynamic Calculation Different Period

Different Calculations

Calculate Difference in week horizon with 2 variables

Calculating Differences