FTE calculation using working days for different time horizons

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