Excluding weekends from the difference of dates.

[Dunner2020]

Hi there,

I have a date table, one of the columns contains the name of week name. I want to calculate the number of days between the start date and end date and exclude the weekend. Below is the formula:

Difference in days = CALCULATE( COUNTROWS(Dates), FILTER(ALL(Dates), Dates[Date] >= start_date &&

Dates[Date] <= end_date &&  Dates[IsHoliday] = FALSE() &&  (Dates[Day Of Week Name] <> "Saturday" &&

Dates[Day Of Week Name] <> "Sunday")  )  )

However, it did not return the right number of days. Could anyone guide me where am I made the mistake?

[pranit828]

Hi @Dunner2020 

Can you please post some sample data for atleast 10 days?

[Dunner2020]

Hi @pranit828 ,

Here is the sample data:

Date	Year	Quarter Of Year	Month Of Year	Day Of Month	DateInt	Month Name	Month In Calendar	Quarter In Calendar	Day In Week	Day Of Week Name	Week Ending	Week Number	Month In Year	QuarternYear	Short Year	RY	IsHoliday
1/01/2019	2019	1	1	1	20190101	January	Jan 2019	Q1 2019	1	Tuesday	########	1	20190100	20190100	19	RY19	False
2/01/2019	2019	1	1	2	20190102	January	Jan 2019	Q1 2019	2	Wednesday	########	1	20190100	20190100	19	RY19	False
3/01/2019	2019	1	1	3	20190103	January	Jan 2019	Q1 2019	3	Thursday	########	1	20190100	20190100	19	RY19	False
4/01/2019	2019	1	1	4	20190104	January	Jan 2019	Q1 2019	4	Friday	########	1	20190100	20190100	19	RY19	False
5/01/2019	2019	1	1	5	20190105	January	Jan 2019	Q1 2019	5	Saturday	########	1	20190100	20190100	19	RY19	False
6/01/2019	2019	1	1	6	20190106	January	Jan 2019	Q1 2019	6	Sunday	########	1	20190100	20190100	19	RY19	False
7/01/2019	2019	1	1	7	20190107	January	Jan 2019	Q1 2019	0	Monday	########	2	20190100	20190100	19	RY19	False
8/01/2019	2019	1	1	8	20190108	January	Jan 2019	Q1 2019	1	Tuesday	########	2	20190100	20190100	19	RY19	False
9/01/2019	2019	1	1	9	20190109	January	Jan 2019	Q1 2019	2	Wednesday	########	2	20190100	20190100	19	RY19	False
10/01/2019	2019	1	1	10	20190110	January	Jan 2019	Q1 2019	3	Thursday	########	2	20190100	20190100	19	RY19	False
11/01/2019	2019	1	1	11	20190111	January	Jan 2019	Q1 2019	4	Friday	########	2	20190100	20190100	19	RY19	False
12/01/2019	2019	1	1	12	20190112	January	Jan 2019	Q1 2019	5	Saturday	########	2	20190100	20190100	19	RY19	False
13/01/2019	2019	1	1	13	20190113	January	Jan 2019	Q1 2019	6	Sunday	########	2	20190100	20190100	19	RY19	False
14/01/2019	2019	1	1	14	20190114	January	Jan 2019	Q1 2019	0	Monday	########	3	20190100	20190100	19	RY19	False
15/01/2019	2019	1	1	15	20190115	January	Jan 2019	Q1 2019	1	Tuesday	########	3	20190100	20190100	19	RY19	True
16/01/2019	2019	1	1	16	20190116	January	Jan 2019	Q1 2019	2	Wednesday	########	3	20190100	20190100	19	RY19	False
17/01/2019	2019	1	1	17	20190117	January	Jan 2019	Q1 2019	3	Thursday	########	3	20190100	20190100	19	RY19	False
18/01/2019	2019	1	1	18	20190118	January	Jan 2019	Q1 2019	4	Friday	########	3	20190100	20190100	19	RY19	False
19/01/2019	2019	1	1	19	20190119	January	Jan 2019	Q1 2019	5	Saturday	########	3	20190100	20190100	19	RY19	False
20/01/2019	2019	1	1	20	20190120	January	Jan 2019	Q1 2019	6	Sunday	########	3	20190100	20190100	19	RY19	False
21/01/2019	2019	1	1	21	20190121	January	Jan 2019	Q1 2019	0	Monday	########	4	20190100	20190100	19	RY19	False
22/01/2019	2019	1	1	22	20190122	January	Jan 2019	Q1 2019	1	Tuesday	########	4	20190100	20190100	19	RY19	False
23/01/2019	2019	1	1	23	20190123	January	Jan 2019	Q1 2019	2	Wednesday	########	4	20190100	20190100	19	RY19	False
24/01/2019	2019	1	1	24	20190124	January	Jan 2019	Q1 2019	3	Thursday	########	4	20190100	20190100	19	RY19	False
25/01/2019	2019	1	1	25	20190125	January	Jan 2019	Q1 2019	4	Friday	########	4	20190100	20190100	19	RY19	False
26/01/2019	2019	1	1	26	20190126	January	Jan 2019	Q1 2019	5	Saturday	########	4	20190100	20190100	19	RY19	False
27/01/2019	2019	1	1	27	20190127	January	Jan 2019	Q1 2019	6	Sunday	########	4	20190100	20190100	19	RY19	False
28/01/2019	2019	1	1	28	20190128	January	Jan 2019	Q1 2019	0	Monday	########	5	20190100	20190100	19	RY19	False
29/01/2019	2019	1	1	29	20190129	January	Jan 2019	Q1 2019	1	Tuesday	########	5	20190100	20190100	19	RY19	False
30/01/2019	2019	1	1	30	20190130	January	Jan 2019	Q1 2019	2	Wednesday	########	5	20190100	20190100	19	RY19	False
31/01/2019	2019	1	1	31	20190131	January	Jan 2019	Q1 2019	3	Thursday	########	5	20190100	20190100	19	RY19	False

[CNENFRNL]

A mimic of Excel NETWORKDAYS func

Networkdays PBI = 
COUNTROWS (
    CALCULATETABLE(
        VALUES('Calendar'[Date]),
        DATESBETWEEN (
            Calendar[Date],
            MIN(Calendar[Date]),
            MAX(Calendar[Date])
        ),
        NOT WEEKDAY ( Calendar[Date] ) IN { 1, 7 },
        NOT 'Calendar'[IsHoliday]
    )
)

[pranit828]

HI @Dunner2020 

It is missing Dates[IsHoliday] column.

[Dunner2020]

@pranit828 ,

That's strange. Because I can see IsHoliday column. IsHoliday contains either False or True Values.In the sample, all values of IsHoliday is false except one date i.e. 15/1/2019

[pranit828]

HI @Dunner2020 

My bad, I didn't scroll to the right.

I got 22 as a result. My [Is Holiday] column is Text and used the below formula. You can change the [start date], [end date] and make sure they are in the correct format and compared with Dates[Date] column.

Difference in days = CALCULATE( COUNTROWS(Dates), FILTER(ALL(Dates)
                            , Dates[Date] >= DATE(2019,01,01) &&
                              Dates[Date] <= date(2019,01,31) &&  Dates[IsHoliday] = "FALSE" 
                              &&  (Dates[Day Of Week Name] <> "Saturday" &&
Dates[Day Of Week Name] <> "Sunday")  )  )

[Dunner2020]

@pranit828 , When I ran your code, visualization did not work. I think the reason might be ISHoliday is a calculated column Which has either value 'False' or 'True'. In my code, I was checking holiday condition like date[ISHoliday]=False(), which you changed it to date[ISHoliday]="FALSE". 

[Greg_Deckler]

@Dunner2020 - I like @pranit828 's solution, I do want to point out that Net Work Days was invented to solve this kind of thing. https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362#M109