Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Help: Calculating Duration in hours excluding weekends

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?

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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.

5 Replies

  • prateekraina's avatar
    prateekraina
    Memorable Member
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
        prateekraina
        Memorable Member
        9 years ago

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