WorkingDays Logic Issue

[Anonymous]

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

[v-yalanwu-msft · Accepted solution]

Hi， @Anonymous ；

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.

[mahoneypat]

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

[Anonymous]

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 

[Anonymous]

Any suggestions please 

[VahidDM]

Hi @Anonymous 

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✌️!!

[Anonymous]

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".

[VahidDM]

Hi @Anonymous 

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🙏!!

[Anonymous]

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