Dynamic Net Capacity Measurement

[KimStahl94]

Hi,

I am currently trying to calculate the net capacity of each employee from the beginning of the year until Today. I am facing the problem that each employee works at different days and for different durations (i.e. working students or part time jobs).
Therefore I have a table that contains the date of change of the changed working hours. So in my current example the measure should use the first seven lines (working hours since 2021 Nov 11th) to calculate the capacity of january 2022 and the second seven lines to calculate the remaining capacity until today.

I have already tried to generate a table that fills the dates between to dates of change. But since I generate a huge table this way (>100 employees with about 15 years of employment), I would like to work around with a measure. But my attempts have been unsuccessful so far.
I appreciate any help!
Regards
KS

[v-shex-msft]

HI @KimStahl94,

Any update for these? if your table has huge amount of records, it should not suitable to generate full records that crossjoin to each employee.
I'd like to suggest you create a full calendar table and use its date as axis, then you can use the measure formula to extract the current date as a condition to look up raw table records.

Regards,

Xiaoxin Sheng

[KimStahl94]

Hello Xiaoxin Sheng,

I am sorry that I haven't updated this post for so long. I have some troubles sharing dummy data becaue I am working with a model that I have not created myself. If I try to only share relevant tables and or anonymize entries the model won't work. But I have some good news, too beacuse I managed to calculate the net capacity as a measure as you mentioned before. It works as follows:

Brutto Capacity 0 =

var DayInContext = MIN ( DateofEffort[Date] )

var WeekNumInContext = WEEKDAY ( DayInContext , 2 )

var DateOfChangeInContext = CALCULATE ( CALCULATE ( MAX ( DynUser[start] ) , FILTER ( DynUser , DynUser[start] <= DayInContext) ) , CROSSFILTER ( 'Efforts'[Employee_oid] ,'Employees'[Employee_oid] , NONE ) )

RETURN

CALCULATE (

CALCULATE (

IF (

MONTH ( DayInContext ) = 12 && DAY ( DayInContext ) = 24 && WEEKDAY ( DayInContext , 2 ) <> 6 && WEEKDAY ( DayInContext , 2 ) <> 7 && MIN ( DynUser[WorkingHours] ) <> 0 ,

SUM ( DynUser[WorkingHours] ) - 4 ,

IF (

MONTH ( DayInContext ) = 12 && DAY ( DayInContext ) = 31 && WEEKDAY ( DayInContext , 2 ) <> 6 && WEEKDAY ( DayInContext , 2 ) <> 7 && MIN ( DynUser[WorkingHours] ) <> 0 ,

SUM ( DynUser[WorkingHours] ) - 4 ,

SUM ( DynUser[WorkingHours] ) ) ) ,

FILTER ( 

DynUser ,

DynUser[start] = DateOfChangeInContext &&

DynUser[Wochentag_Num] = WeekNumInContext ) ) ,

CROSSFILTER ( 'Efforts'[Employee_oid] ,'Employees'[Employee_oid] , NONE ) )

I am basically defining variables and extracting the concrete value for the supposed working hours on a daily

basis for each employee. I am subtracting 4 hours on 31th Dec and 24th Dez due to company wide half days off on these days. As you can see I am also disabling crossfilters what I need to do due to the given data model. In another measure I am defining the NonWorkDays due to holidays, vacations and days of sickness which I then subtract to caluclate the Net Capacity. An issue with the given measure is the filter context leading to wrong totals in my table visualization. To show the correct totals for each employee and also correct totals I am wrapping the measure in two SUMX functions:

Brutto Capacity 1 =

CALCULATE (

SUMX ( VALUES ( 'DateofEffort'[Date] ) , [Brutto Capacity 0] ) ,

CROSSFILTER ( 'Efforts'[Employee_oid] ,'Employees'[Employee_oid] , NONE ) )

and

Brutto Capacity =

CALCULATE (

SUMX ( VALUES ( 'Employees'[Employee] ) , [Brutto Capacity1] ) ,

CROSSFILTER ('Efforts'[Employee_oid] ,'Employees'[Employee_oid] , NONE ) )

With those measures I am now able to calculate the net capacity for each employee while selecting different time periods.

It works as it should but I am still not very content with my measure because of the 3-step solution. What is your opinion on that?

Sorry again for not answering. Regards

KS 

[v-shex-msft · Accepted solution]

HI @KimStahl94,

First, I'd like to suggest you add a table to store the excepted scenario and workhour offset instead of hardcoded this processing in your DAX expressions.

Index	Date	Offset
1	12/24/2021	-4
2	12/31/2021	-4

You can add a simple sub-formula to looping this new table and calculate with current formulas. (check the new table records if they include in current calculation date range and summary suitable row values, then use current work hour to minus them)

Second, the Dax function which is used to get the week number is weeknum. (weekday is used to get the day of the week)

Here is the formula I try to simple and add the calculation group fields. (notice: the work hour offset is still balanced by hardcoded because your data model does not include a table to store the excepted dates)

Brutto Capacity =
VAR DayInContext =
    MIN ( DateofEffort[Date] )
VAR WeekNumInContext =
    WEEKNUM ( DayInContext, 2 )
VAR DateOfChangeInContext =
    CALCULATE (
        CALCULATE (
            MAX ( DynUser[start] ),
            FILTER ( DynUser, DynUser[start] <= DayInContext )
        ),
        CROSSFILTER ( 'Efforts'[Employee_oid], 'Employees'[Employee_oid], NONE )
    )
RETURN
    CALCULATE (
        SUM ( DynUser[WorkingHours] )
            + IF (
                MONTH ( DayInContext ) = 12
                    && DAY ( DayInContext )
                        IN { 24, 31 }
                            && WEEKDAY ( DayInContext, 2 ) < 6
                            && MIN ( DynUser[WorkingHours] ) <> 0,
                -4
            ),
        FILTER (
            DynUser,
            DynUser[start] = DateOfChangeInContext
                && DynUser[Wochentag_Num] = WeekNumInContext
        ),
        VALUES ( 'Employees'[Employee] ),
        VALUES ( 'DateofEffort'[Date] ),
        CROSSFILTER ( 'Efforts'[Employee_oid], 'Employees'[Employee_oid], NONE )
    )

Regards,

Xiaoxin Sheng

[v-shex-msft]

Hi @KimStahl94,

Can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng