Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.

Reply
WLFRD
Helper III
Helper III

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

 

Capture.JPG

 

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

 

Thanks in advance

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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
Super User
Super User

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

 

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard
Arul
Super User
Super User

@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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


LinkedIn


Helpful resources

Announcements
Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

Find out what's new and trending in the Fabric community.