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

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors