Forum Discussion

WLFRD's avatar
WLFRD
Helper III
4 years ago
Solved

WorkingDays - Excluded weekends

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_DATED_PROM_ENTER_DATEDateDiff
07-Mar-2231-Mar-2224
09-Mar-2225-Mar-2216
15-Mar-2231-Mar-2216
08-Mar-2221-Mar-2213
02-Mar-2214-Mar-2212
10-Mar-2222-Mar-2212
07-Mar-2218-Mar-2211
04-Mar-2214-Mar-2210
16-Mar-2225-Mar-229
08-Mar-2216-Mar-228
14-Mar-2222-Mar-228
14-Mar-2222-Mar-228
03-Mar-2211-Mar-228
21-Mar-2229-Mar-228
10-Mar-2217-Mar-227
02-Mar-2209-Mar-227
18-Mar-2225-Mar-227
10-Mar-2217-Mar-227
18-Mar-2225-Mar-227
10-Mar-2217-Mar-227
02-Mar-2208-Mar-226
25-Mar-2231-Mar-226
09-Mar-2215-Mar-226
09-Mar-2215-Mar-226
03-Mar-2209-Mar-226
09-Mar-2215-Mar-226
04-Mar-2210-Mar-226
23-Mar-2228-Mar-225
10-Mar-2215-Mar-225
03-Mar-2208-Mar-225
23-Mar-2228-Mar-225
23-Mar-2228-Mar-225
09-Mar-2214-Mar-225
04-Mar-2208-Mar-224
04-Mar-2208-Mar-224
03-Mar-2207-Mar-224
10-Mar-2214-Mar-224
01-Apr-2204-Apr-223
25-Mar-2228-Mar-223
25-Mar-2228-Mar-223

 

 

I would like to exclude the weekends (Saturday and Sunday). Can someone help me out?

 

Thanks in advance

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

3 Replies

  • Tahreem24's avatar
    Tahreem24
    Super User
    4 years ago

    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.

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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