WorkingDays - Excluded weekends

[WLFRD]

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

[v-shex-msft · Accepted 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

[Tahreem24]

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

[Arul]

@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