Networkingdays per Employee based on their respective Working Model

[JKross]

Hi all,

I hope someone can help. I have to calculate the Networkingdays per employee based on their respective Working Model and the Bank Holidays. A Working Model defines which weekdays are the working days.

I have 4 Tables:
Table Calendar: Date I Weekday, whereas Monday is 1, Tuesday 2, Wednesday 3, Thursday 4, Friday 5
Table Bank Holidays: Bank holiday I Date

Table Employees: EmployeeID I WorkingsModelID

Table Working Model: WorkingModelID I Workingdays

In the Table Working Model the column Workingdays is a textstring containing the weekdays which are the repsective Workingdays it looks (examples) like this:
WorkingModelID     I   Workingdays
Mo-Fr 40hrs             I   1,2,3,4,5
Mo-Fr 30hrs             I   1,2,3,4,5
Mo-Th 20hrs            I   1,2,3,4
Mo-Tu 16hrs            I   1,2
Mo-Th 28hrs            I   1,2,3,4

Tables Calendar and Bank Holidays are connected via Date.

Tables Employees and Working Model are connected via WorkingModelID.

I tried out a few ways but did not get there - any ideas?

[Anonymous]

Hi, @JKross 

Thanks for the reply from johnt75 and bhanu_gautam. I think I'll merge the Employees and Working Model tables into one table as shown below.

Networkingdays =
SUM ( Employees[Monday] ) + SUM ( Employees[Tuesday] )
    + SUM ( Employees[Wednesday] )
    + SUM ( Employees[Thursday] )
    + SUM ( Employees[Friday] )

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[johnt75]

I would make some changes to the model. Remove the relationship between Calendar and Bank Holidays and instead add a new column into Calendar to indicate whether it is a Bank Holiday or not. You could do this in DAX like

Is Bank Holiday =
'Calendar'[Date] IN VALUES ( 'Bank Holiday'[Date] )

Transform your Working Days table to split the comma separated list of days, so that you one entry per day, and so multiple entries for each Working Model. You would not need a relationship between Working Model and Employees.

You could then write a measure for net working days like

Net working days =
SUMX (
    Employees,
    VAR EmpID = Employees[Employee ID]
    VAR WorkingModelID = Employees[Working Model ID]
    VAR WorkingDays =
        CALCULATETABLE (
            VALUES ( 'Working Model'[Working Days] ),
            'Working Model'[Working Model ID] = WorkingModelID
        )
    VAR Result =
        CALCULATE (
            COUNTROWS ( 'Calendar' ),
            KEEPFILTERS ( TREATAS ( WorkingDays, 'Calendar'[Weekday] ) ),
            KEEPFILTERS ( 'Calendar'[Is Bank Holiday] = FALSE () )
        )
    RETURN
        Result
)

[JKross]

Hi @johnt75 ,

great idea. I converted Calendar with the Bank holidays to true and false. And I converted my WorkingModel Table so that it looks like this - ie. adding 5 columns for the Working days:

WorkingModelID     I   WD1    I WD2     I WD3     I WD4     IWD 5
Mo-Fr 40hrs             I   1          I 2           I3            I 4           I 5
Mo-Fr 30hrs             I   1          I 2           I3            I 4           I 5
Mo-Th 20hrs            I   1          I 2           I3            I 4           
Mo-Tu 16hrs            I   1          I 2
Mo-Th 28hrs            I  1          I 2           I3            I 4 
We 8hrs                    I 3
Tu,We,Fr 24hrs          I 2          I 3            I 5

I inserted the NetWorkingdays Measure in the Calendar table - correct?

I get stuck in in the CALCULATETABLE step - the Workingdays are now in multiple colums, where some entries might be empty. VALUES ('WorkingModels [Workingdays]) is not there anymore.

How do I link in the new table?

[johnt75 · Accepted solution]

The Calendar table is fine as a location for the measure, it doesn't really matter which table you put it in.

I would unpivot the working days table so that there are only 2 columns, Working Model ID and Working Day. There will be multiple rows for each Working Model ID but that is OK.

[JKross]

Hi @johnt75 ,

thank you so much for your feedback! I am getting close! Last thing that remains is that now the Result for a Non-Working Day according to the Working Model is 1 and for a Working Day according to the Working Model is 2.

I am checking why that is, do you have any idea?

Worst Case I will substract 1, but I prefer to find the source why it adds 1 Working Day...

[johnt75]

Can you share a sample PBIX? You can share a link from Google Drive or OneDrive or similar

[JKross]

HI @johnt75 ,

found it! The thing is, that in the employee data I have multiple data records, which are distinct in their Valid from and Valid to Date. The Example I was looking at had 2 data records, hence it always added one. I need now to refer the calculation to the Valid from and Valid to Date. Since the working Model can change, I need to take the Working model into account valid on this date.

If you have a good suggestion where to factor this step in shoot ;-)... I am on it!

[JKross]

@johnt75  I got it! I just inserted another filter in the Countrows to validate if the date is > than Validfrom and <= Validto Date.

Thank you so much for your sparring, Input and Ideas!!!

[johnt75]

We're making progress.

You can factor the valid dates in like

Net working days =
SUMX (
    Employees,
    VAR EmpID = Employees[Employee ID]
    VAR WorkingModelID = Employees[Working Model ID]
    VAR WorkingDays =
        CALCULATETABLE (
            VALUES ( 'Working Model'[Working Days] ),
            'Working Model'[Working Model ID] = WorkingModelID
        )
    VAR ValidDates =
        DATESBETWEEN ( 'Calendar'[Date], Employees[Valid From], Employees[Valid To] )
    VAR Result =
        CALCULATE (
            COUNTROWS ( 'Calendar' ),
            KEEPFILTERS ( TREATAS ( WorkingDays, 'Calendar'[Weekday] ) ),
            KEEPFILTERS ( 'Calendar'[Is Bank Holiday] = FALSE () ),
            KEEPFILTERS ( ValidDates )
        )
    RETURN
        Result
)

[JKross]

Hi @johnt75 I'd love to, but it is technically impossible unfortunately... I am following all steps and got seemingly blind... Let me see what I can extract. If you give me hints what you need I will take special care of it.

[JKross]

Hi @johnt75  - strangest thing of all: I extracted some data and set up a sample PBIX, and there the calculation works out perfectly! I think I have to follow the steps with the real data myself... Thank you for the hint...

[bhanu_gautam]

@JKross First ensure that the relationships between the tables are correctly set up in the Power BI model.

Add a calculated column in the Calendar table to identify if a day is a working day for each working model.

WorkingDay =
VAR CurrentDay = 'Calendar'[Weekday]
VAR WorkingDaysString =
CALCULATE(
MAX('Working Model'[Workingdays]),
RELATED('Employees'[WorkingModelID]) = 'Working Model'[WorkingModelID]
)
RETURN
IF(
CONTAINSSTRING(WorkingDaysString, FORMAT(CurrentDay, "0")),
1,
0
)

Create a measure to count the working days excluding bank holidays.

DAX
Networkingdays =
CALCULATE(
COUNTROWS('Calendar'),
'Calendar'[WorkingDay] = 1,
NOT(
'Calendar'[Date] IN VALUES('Bank Holidays'[Date])
)
)

To get the Networkingdays per employee, you can use the measure in a table or matrix visual with EmployeeID.

[JKross]

Hi @bhanu_gautam,

Thank you! Unfortunately I have 2 Problems with this:
a) I have (unfortunately) 41 WorkingModels as they are defined by their Workingdays and Hours per week => I would like to avoid adding 41 caluclated colums

b) The Calendar Table is and cannot be connected to the Employee table. This in the VAR WorkingDaysString I cannot select the "RELATED('Employees'[WorkingModelID]) = 'Working Model'[WorkingModelID]"

I tried something similar before, but it failed as well - I think because the Working Models are not unique in their Working days. Here is what I tried in the Calendar Table per Date:
Is_Workingday =
VAR (WeekdayNo) = Calendar (Weekday)
RETURN
If(
    CONTAINSSTRING(
         SELECTEDVALUE(WorkingModels[Workingdays]),

         FORMAT(WeekdayNo,"0")
     ),
1,
0)
The calculated table shows 0 for all dates. I think it is becaus the weekday is contained in multiple Working Models, thus textstrings.

Is there a way to calculate it in a measure?

[bhanu_gautam]

@JKross , I agree with @johnt75 , It would be better if you create one table instead of multiple