cancel
Showing results for
Search instead for
Did you mean:
Frequent Visitor

Calculated Column Showing Business days count between 2 dates

I have a table that has 'Created' date/time and 'Updated' date/time.

I can calculate the DATEDIFF, but need to also show Business Days Only.

I have a CalendarTbl[Date] with relationship to below data.

Example of data

Created                             Updated
03-05-2021 11:22:09 AM |03-05-2021 02:53:52 PM
03-02-2021 09:41:13 PM | 04-05-2021 09:40:50 PM
02-24-2021 09:13:02 AM | 04-12-2021 02:52:56 PM
02-16-2021 04:09:22 PM | 02-22-2021 10:44:02 AM
02-15-2021 04:08:22 PM | 03-31-2021 11:14:54 AM

Any help would be greatly appreciated.

tks/

1 ACCEPTED SOLUTION
Super User IV

@cs566 , Something like this

Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Table[Created],Table[updated]),"WorkDay", if(not(WEEKDAY([Date],1) in {1,7}),1,0)),[WorkDay] =1))

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

4 REPLIES 4
Frequent Visitor

Thank you Amit.  Your video was helpful and I will look at your other videos.

One question... how would that Sample column formula work if Sunday=1 and Saturday=7?

I am very new to DAX and just trying to learn as I go.

tks/

Super User IV

@cs566 , Something like this

Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Table[Created],Table[updated]),"WorkDay", if(not(WEEKDAY([Date],1) in {1,7}),1,0)),[WorkDay] =1))

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Frequent Visitor

Thank you so much!  Worked great!

Super User IV

@cs566 , Refer my Video on the same if that can help

https://www.youtube.com/watch?v=Qv4wT8_P-AA

Sample column formula

Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Table[Created],Table[updated]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors