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

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

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
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
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
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

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

Top Solution Authors
Top Kudoed Authors