Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers!
Enter the sweepstakes now!Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.
Hello,
I would like to get some help with the nex issue. I have a table with to dates. A simple Datediff wil calculate the day diffencence between the two given dates in the table.
D_ENTER_DATE | D_PROM_ENTER_DATE | DateDiff |
07-Mar-22 | 31-Mar-22 | 24 |
09-Mar-22 | 25-Mar-22 | 16 |
15-Mar-22 | 31-Mar-22 | 16 |
08-Mar-22 | 21-Mar-22 | 13 |
02-Mar-22 | 14-Mar-22 | 12 |
10-Mar-22 | 22-Mar-22 | 12 |
07-Mar-22 | 18-Mar-22 | 11 |
04-Mar-22 | 14-Mar-22 | 10 |
16-Mar-22 | 25-Mar-22 | 9 |
08-Mar-22 | 16-Mar-22 | 8 |
14-Mar-22 | 22-Mar-22 | 8 |
14-Mar-22 | 22-Mar-22 | 8 |
03-Mar-22 | 11-Mar-22 | 8 |
21-Mar-22 | 29-Mar-22 | 8 |
10-Mar-22 | 17-Mar-22 | 7 |
02-Mar-22 | 09-Mar-22 | 7 |
18-Mar-22 | 25-Mar-22 | 7 |
10-Mar-22 | 17-Mar-22 | 7 |
18-Mar-22 | 25-Mar-22 | 7 |
10-Mar-22 | 17-Mar-22 | 7 |
02-Mar-22 | 08-Mar-22 | 6 |
25-Mar-22 | 31-Mar-22 | 6 |
09-Mar-22 | 15-Mar-22 | 6 |
09-Mar-22 | 15-Mar-22 | 6 |
03-Mar-22 | 09-Mar-22 | 6 |
09-Mar-22 | 15-Mar-22 | 6 |
04-Mar-22 | 10-Mar-22 | 6 |
23-Mar-22 | 28-Mar-22 | 5 |
10-Mar-22 | 15-Mar-22 | 5 |
03-Mar-22 | 08-Mar-22 | 5 |
23-Mar-22 | 28-Mar-22 | 5 |
23-Mar-22 | 28-Mar-22 | 5 |
09-Mar-22 | 14-Mar-22 | 5 |
04-Mar-22 | 08-Mar-22 | 4 |
04-Mar-22 | 08-Mar-22 | 4 |
03-Mar-22 | 07-Mar-22 | 4 |
10-Mar-22 | 14-Mar-22 | 4 |
01-Apr-22 | 04-Apr-22 | 3 |
25-Mar-22 | 28-Mar-22 | 3 |
25-Mar-22 | 28-Mar-22 | 3 |
I would like to exclude the weekends (Saturday and Sunday). Can someone help me out?
Thanks in advance
Solved! Go to Solution.
HI @WLFRD,
You can use countrows and weekday functions to create a calculated column to achieve your requrement:
Datediff =
COUNTROWS (
FILTER (
CALENDAR ( Table[D_ENTER_DATE], Table[D_PROM_ENTER_DATE] ),
WEEKDAY ( [Date], 2 ) < 6
)
)
Regards,
Xiaoxin Sheng
HI @WLFRD,
You can use countrows and weekday functions to create a calculated column to achieve your requrement:
Datediff =
COUNTROWS (
FILTER (
CALENDAR ( Table[D_ENTER_DATE], Table[D_PROM_ENTER_DATE] ),
WEEKDAY ( [Date], 2 ) < 6
)
)
Regards,
Xiaoxin Sheng
@WLFRD TRy this DAX column:
Column =
VAR weekday = WEEKDAY(DateColumn,2)
RETURN IF(weekday in {6,7},0,1)
So, all the rows with 1 are from Monday to Friday. You can filter out 0 in order to exclude weekend.
@WLFRD ,
Try the below approach provide in the link and let me know if that does not helps.
https://community.powerbi.com/t5/Desktop/DATEDIFF-excluding-weekends/m-p/947915
Thanks,
Arul
User | Count |
---|---|
72 | |
68 | |
67 | |
44 | |
42 |
User | Count |
---|---|
47 | |
38 | |
28 | |
27 | |
27 |