Reply
avatar user
Anonymous
Not applicable
Partially syndicated - Outbound

Calculate the difference between two dates and two hours excluding days/WE

Hi Microsoft community,

 

I am a beginner with Power BI (I usually use Excel only)

 

I need to calculate 2 things :

- The number of hours between 2 dates and times, excluding WE and holidays.

- The number of days between 2 dates, excluding WE and holidays

The format of the two dates are like this : 

Date d'envoi

    Date approbation

28/02/2020 15:36:38

    02/03/2020 10:56:55

02/03/2020 11:12:45

    02/03/2020 11:18:32

I need to do [Date approbation]-[Date d'envoi] excluding WE/holidays

I already have a table with the list of all the WE and holidays . 

 

Keep in mind that I am a beginner so if you have a simple solution, it will be great. I like to understand the solution and not only copy it.

 

Thank you very much !

 

Best regards,

Alex

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

Syndicated - Outbound

@Anonymous , You can find out work days like this

a new column

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

 

You can also refer this for business refer

https://exceleratorbi.com.au/calculating-business-hours-using-dax/

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

2 REPLIES 2
v-jingzhang
Community Support
Community Support

Syndicated - Outbound

Hi @Anonymous 

You can first refer to below blogs about how to count working days and working hours. You may need some time to learn the functions and concepts involved. 

Counting working days in DAX - SQLBI

Power BI DATEDIFF only working days, hours and so on... • Tomasz Poszytek, Business Applications MVP

 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

amitchandak
Super User
Super User

Syndicated - Outbound

@Anonymous , You can find out work days like this

a new column

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

 

You can also refer this for business refer

https://exceleratorbi.com.au/calculating-business-hours-using-dax/

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)