Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

WorkingDays Logic Issue

Dear All,

My requirement is to calculate WorkingDays between two dates excluding weekends.

 

startdateenddateWorkingDays
4/6/20204/7/20202
5/21/2020 0
5/21/20205/21/20201
5/21/20205/29/20207

 

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

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    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.

8 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        Anonymous
        Not applicable
        4 years ago

        Any suggestions please 

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    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.