Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Dear All,
My requirement is to calculate WorkingDays between two dates excluding weekends.
startdate | enddate | WorkingDays |
4/6/2020 | 4/7/2020 | 2 |
5/21/2020 | 0 | |
5/21/2020 | 5/21/2020 | 1 |
5/21/2020 | 5/29/2020 | 7 |
below expression is giving error, "The start date or end date in Calendar function can not be Blank value."
WorkingDays =
VAR YourDate = if(Table[enddate]= BLANK(),BLANK(),Table[enddate])
RETURN
COUNTROWS (
FILTER (
ADDCOLUMNS ( CALENDAR ( Table[startdate], YourDate ), "Day of Week", WEEKDAY ( [Date], 1) ),
[Day of Week] <> 1
&& [Day of Week] <> 7
)
)
Please guide me.
Suren
Solved! Go to Solution.
Hi, @suren947 ;
You could modify the dax as follows:
WorkingDays =
VAR YourDate =
IF ( [enddate] = BLANK (), [startdate], [enddate] )
var _count=
COUNTROWS (
FILTER (
ADDCOLUMNS (
CALENDAR ( [startdate], YourDate ),
"Day of Week", WEEKDAY ( [Date], 1 )),
[Day of Week] <> 1&& [Day of Week] <> 7) )
return IF([enddate]=BLANK(),0,_count)
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @suren947 ;
You could modify the dax as follows:
WorkingDays =
VAR YourDate =
IF ( [enddate] = BLANK (), [startdate], [enddate] )
var _count=
COUNTROWS (
FILTER (
ADDCOLUMNS (
CALENDAR ( [startdate], YourDate ),
"Day of Week", WEEKDAY ( [Date], 1 )),
[Day of Week] <> 1&& [Day of Week] <> 7) )
return IF([enddate]=BLANK(),0,_count)
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Please try this column expression instead. Replace WD with your actual table name.
WDays =
VAR enddate =
IF ( ISBLANK ( WD[enddate] ), TODAY (), WD[enddate] )
VAR result =
COUNTROWS (
FILTER (
CALENDAR ( WD[startdate], enddate ),
WEEKDAY ( [Date] ) IN { 1, 2, 3, 4, 5 }
)
)
RETURN
IF ( ISBLANK ( WD[enddate] ), 0, result )
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thank you Pat. your expression works but not expected
when there is any future dates in startdate i.e startdate greater than enddate (startdate>enddate) ex:(9/10/2021 > 9/8/2021).
If there is any future dates in startdate make startdate as blank and also for any blanks in either startdate or enddate we replace them with blanks/0 but not with Today()
Please suggest
Any suggestions please
Hi @suren947
Try to add another condition to @mahoneypat measure, if you want to show 0, when startdate is grater than end date or one of those date items is blank:
WDays =
VAR enddate =
IF ( ISBLANK ( WD[enddate] ), TODAY (), WD[enddate] )
VAR result =
COUNTROWS (
FILTER (
CALENDAR ( WD[startdate], enddate ),
WEEKDAY ( [Date] ) IN { 1, 2, 3, 4, 5 }
)
)
RETURN
IF ( WD[startdate]>WD[enddate]||ISBLANK ( WD[enddate] )||ISBLANK ( WD[startdate] ), 0, result )
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos✌️!!
Thank you Vahid for your reply but the expression is showing below message. Please help
"The start date in Calendar function can not be later than the end date".
Hi @suren947
Try this column expression and replace WD with your actual table name.
WDays =
IF (
WD[startdate] > WD[enddate]
|| ISBLANK ( WD[enddate] )
|| ISBLANK ( WD[startdate] ),
0,
COUNTROWS (
FILTER (
CALENDAR ( WD[startdate], WD[enddate] ),
WEEKDAY ( [Date] ) IN { 1, 2, 3, 4, 5 }
)
)
)
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos🙏!!
Thank you Vahid for your responses. But i am unable to get correct values as shown in column
NETWORKDAYS(A2,B2)-1 |
Could you please help me here
startdate | enddate | WDays | NETWORKDAYS(A2,B2)-1 |
4/6/2020 | 4/7/2020 | 2 | 1 |
5/21/2020 | 0 | 0 | |
5/21/2020 | 5/21/2020 | 1 | 0 |
5/21/2020 | 5/29/2020 | 42 | 6 |
5/21/2020 | 6/3/2020 | 220 | 9 |
5/22/2020 | 0 | 0 | |
5/22/2020 | 6/3/2020 | 387 | 8 |
5/26/2020 | 0 | 0 | |
6/18/2020 | 6/18/2020 | 6 | 0 |
8/18/2021 | 8/24/2021 | 65 | 4 |
8/18/2021 | 8/25/2021 | 42 | 5 |
8/18/2021 | 8/26/2021 | 77 | 6 |
8/19/2021 | 0 | 0 | |
8/19/2021 | 8/19/2021 | 355 | 0 |
8/19/2021 | 8/20/2021 | 77 | 1 |
8/19/2021 | 8/21/2021 | 9 | 1 |
8/19/2021 | 8/23/2021 | 45 | 2 |
8/19/2021 | 8/25/2021 | 25 | 4 |
8/20/2021 | 0 | 0 |
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |