Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 &&
Hi @Dunner2020
Can you please post some sample data for atleast 10 days?
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
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 |
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]
)
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
HI @Dunner2020
It is missing Dates[IsHoliday] column.
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
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
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") ) )
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
@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".
@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