cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cs566
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

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

View solution in original post

4 REPLIES 4
cs566
Frequent Visitor

@amitchandak 

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/

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

View solution in original post

Thank you so much!  Worked great!

amitchandak
Super User IV
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
PBI User Groups

Welcome to the User Group Public Preview

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

MBAS on Demand

Microsoft Business Applications Summit sessions

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

Get Ready for Power BI Dev Camp

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