Calculate the working days (excluding weekends and holidays) between Date logged and Today's Date

[cottrera]

Hi I need to calculate the working days (exlcuding weekends and holidays) between the Date logged and Todays date (See jobs raised able below) I have a calendar table (see calendar table below) which contains a column to show weekends and a column to show holidays .  I also have a the following calculationTodays Date = TODAY()

My recent post (See link) was to Calculate working days between dates - excluding holiday for raised and completed dates. I can only asume the request above is similar, however I am fairly new to DAX and am unable to edit it.

thank you 

Jobs raised table 

Job reference	Date Logged
5417226	23/01/2019
5512901	05/03/2019
5527364	12/03/2019
5533600	15/03/2019
5534913	15/03/2019
5544483	21/03/2019
5561396	01/04/2019
5567261	04/04/2019
5570256	05/04/2019
5570339	05/04/2019
5573713	05/04/2019
5573911	05/04/2019
5574670	05/04/2019
5576543	05/04/2019
5576626	05/04/2019
5576634	05/04/2019
5576775	05/04/2019
5577236	05/04/2019
5577393	05/04/2019
5578804	05/04/2019
5579117	05/04/2019
5587425	10/04/2019
5590840	12/04/2019
5601390	18/04/2019
5603099	23/04/2019
5603221	23/04/2019

PKDate	is_weekend	is_holiday
03/04/2017	0	0
04/04/2017	0	0
05/04/2017	0	0
06/04/2017	0	0
07/04/2017	0	0
08/04/2017	1	0
09/04/2017	1	0
10/04/2017	0	0
11/04/2017	0	0
12/04/2017	0	0
13/04/2017	0	0
14/04/2017	0	1
15/04/2017	1	0
16/04/2017	1	0
17/04/2017	0	1
18/04/2017	0	0
19/04/2017	0	0
20/04/2017	0	0
21/04/2017	0	0
22/04/2017	1	0
23/04/2017	1	0
24/04/2017	0	0
25/04/2017	0	0
26/04/2017	0	0
27/04/2017	0	0

[CheenuSing · Accepted solution]

Hi @cottrera 

Please try the following

1. Create a measure    

                                 NoOfDays = SUMX(LoggedData,DATEDIFF(LoggedData[DateLogged],today(),DAY) )

    This computes the number of lapsed days between logged date and today.

2.  Create a measure

                  NoOfWeekends = 
                                        Var CurLoggedDate = SELECTEDVALUE(LoggedData[DateLogged])
                                           Return
                                              CALCULATE(COUNT(DateTable[WeekEnd]),

                                                       FIlter(DateTable,DateTable [Date]>=CurLoggedDate &&
                                                                                                                            DateTable[Date] <= Today() && 
                                                                                                          DateTable[WeekEnd] = 1) )

         This will check the DateTable for number of weekends between logged date and today.

         So we get the number of weekends.

3.  Create a measure

                  NoOfHolidays= 
                                        Var CurLoggedDate = SELECTEDVALUE(LoggedData[DateLogged])
                                           Return
                                              CALCULATE(COUNT(DateTable[Holiday]),

                                                       FIlter(DateTable,DateTable [Date]>=CurLoggedDate &&
                                                                                                                            DateTable[Date] <= Today() && 
                                                                                                          DateTable[Holiday] = 1) )

         This will check the DateTable for number of Holidays between logged date and today.

         So we get the number of Holidays.

4. Finally create a measure

             NetWorkingDays = [NoOfDays] -[NoOfWeekends] -[NoOfHolidays]

Based on your sample data I changed the date table year to 2019. With the above measures the table output is

Cheers

CheenuSing

[cottrera]

Thank you works fine.