topic Re: Calculate hours between two date/time columns and exclude weekends in DAX Commands and Tips

Calculate hours between two date/time columns and exclude weekends

NDRS — Thu, 01 Feb 2024 08:07:53 GMT

Hi all, first post here so be nice since im a beginner. 🙏

Im trying to caculate the hours between two columns including start date/time and end date/time and I want to exlude the weekends. I have been looking but many questions is about calculate business hours but that's not the case for me.

Can you guys please help a newbie out? 🙂 Thanks!!

Re: Calculate hours between two date/time columns and exclude weekends

NandanHegde — Thu, 01 Feb 2024 08:51:49 GMT

You can identify whether a date is weekday or weekend bbbby below aspect :

if(WEEKDAY([Date],2)>=6,"Weekend","Weekday")

And you can use DATEDIFF to identify the diff in hours between 2 dates:

https://datascientest.com/en/mastering-power-bi-datediff-effortlessly-calculate-time-intervals#:~:text=The%20DAX%20DATEDIFF%20function%20calculates,DATEDIFF%20(%2C%20%2C%20)%E2%80%9D.

Can you also plz state what you mean by exclude weekends

Re: Calculate hours between two date/time columns and exclude weekends

NDRS — Thu, 01 Feb 2024 09:03:51 GMT

Thanks for the reply.

Each row is basicly a ticket with a start and end-date/time (Opened and solved) and I want to calculate the number of hours it takes so solve every ticket. And by weekend I mean I want to exclude the hours from saturday and sunday since we are not measured in the SLA on the weekends. Make this more sense now?

Im familiar with the DATEDIFF formula but if a ticket is opened on a friday and solved on a monday, it will include the hours from the weekend and I don't want it to include the hours from the weekend.

Re: Calculate hours between two date/time columns and exclude weekends

NandanHegde — Thu, 01 Feb 2024 11:40:59 GMT

you can identify the number of weekends between the 2 dates via the below thread :

https://community.fabric.microsoft.com/t5/Desktop/Calculate-number-of-weekends-Fridays-and-Saturdays-between-two/m-p/2751396#M957936

And via datediff get the hours count between the 2 dates and substract the hours based on number of weekends * 24

Re: Calculate hours between two date/time columns and exclude weekends

NDRS — Thu, 01 Feb 2024 12:25:03 GMT

Thanks! Great way to solve it.
I used this formula:

Custom = DATEDIFF('MyTable'[Start date],'MyTable'[End date],HOUR) -

COUNTROWS(FILTER(CALENDAR('MyTable'[Start date],'MyTable'[End date]), WEEKDAY([Date],2) in {6,7} )) * 24