Help: Calculating Duration in hours excluding weekends

[Anonymous]

Hi,

I am calculating the duration between 2 events.

We have an event start date and event end date.

Both fields are DATETIME.

I can calculate the duration between the two events and transform to total hours easily.

How can I remove weekends from the calculation?

[prateekraina]

Hi @Anonymous,

Why dont you use a time dimension table in which you can have 'IsWeekDay' flag.
Create relationship between this dimension table with your other table based on date.
And then use this flag in your duration calculation.

I have just suggested an approach to you, you can try it.

Thanks !!

[Anonymous]

Thanks! Do you have a link to a thread where someone has done this?

I'm quite new to Power BI/BI in general.

[prateekraina]

Hi @Anonymous,

Sorry, not aware of any link.
Just Google DateDimension table and download the SQL script.

In case if that flag is not available then you can create using simple SQL DateTime functions.

Thanks !!

[Anonymous]

After a bit of reading it seems that it would exclude start and end times on the weekend as well.

Just say an event starts on a Thrusday at 14:00 and ends on Monday at 14:00.

I want my duration to be 48 hours and not 96 Hours (48 hours of weekend to be excluded).

Or if an item starts on a Saturday or Sunday the timing must only commence on Monday at 00:00.

Anyone done something like this in Power BI or sql?

[Anonymous · Accepted solution]

Ended up using this.

Worked perfectly.

Datediff(HH, Event_Date_Start, Event_Date_end)
+ CASE WHEN Datepart(dw, Event_Date_Start) = 7 THEN 1 ELSE 0 END
- (Datediff(wk, Event_Date_Start, Event_Date_end) * 48 )
- CASE WHEN Datepart(dw, Event_Date_Start) = 1 THEN 1 ELSE 0 END +
- CASE WHEN Datepart(dw, Event_Date_end) = 1 THEN 1 ELSE 0
end

You can change the 48 to 2 if you want this to work in days.