Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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

PJogo_0-1601482278567.png

 

 

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:

PJogo_0-1601482512663.png

 

The monthly view should be something like:

PJogo_0-1601482341910.png

 

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

1 ACCEPTED SOLUTION
daxer-almighty
Solution Sage
Solution Sage

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

View solution in original post

7 REPLIES 7
daxer-almighty
Solution Sage
Solution Sage

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

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
Not applicable

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!

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

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.

PJogo_0-1603094516929.png

 

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.

PJogo_0-1603096772446.png

 

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

2020-10-19 10_32_42-Random DM - Power BI Desktop.png

 

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
Community Support
Community Support

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:

1.JPG

 

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

2020-10-02 19_08_13-Performance Data Model 3.0 - Dev New FTE Approach - Power BI Desktop.png

 

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.