Forum Discussion

rivastoday's avatar
rivastoday
Frequent Visitor
7 years ago

Help Calculating Business Working hours

Hello Team,

 

Even there is another post with this request I already tried but the answer didnt help me out, I am trying to calculate the working hours between 2 dates using the next formula, the issue I have is the datesbetween doesnt calculate the hours in a correct way, the start time is 7:00 AM and Finish time 7:00 PM:

 

Business Hours = ((CALCULATE(SUM(Calendario[Working Days]),DATESBETWEEN(Calendario[Date],FP[real_Submitted],FP[Real_Closed]))-2)*"09:00")
+("19:00" - MOD(FP[real_Submitted],1))
+MOD(FP[Real_Closed],1)-"07:00"

 

This is my database

 

Real Submit Time            Resolve Time

23/03/2018 16:5302/04/2018 00:06
26/03/2018 07:5202/04/2018 00:08
26/03/2018 08:0002/04/2018 00:19
26/03/2018 07:5202/04/2018 00:26
23/03/2018 11:4702/04/2018 00:35
21/03/2018 08:0002/04/2018 00:37
22/03/2018 09:1102/04/2018 00:37
21/03/2018 08:2502/04/2018 00:43
22/03/2018 16:5603/04/2018 00:02
22/03/2018 13:0903/04/2018 00:02
21/03/2018 08:0003/04/2018 00:03

 

I need to know hot to multiply the network days by 9 hours:

 

NetwdHours = (CALCULATE(SUM(Calendario[Working Days]),DATESBETWEEN(Calendario[Date],FP[real_Submitted],FP[Real_Closed]))-2)*9

 

Thanks,

Need Help

3 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    7 years ago

    Hey,

    I'm wondering how the headers of the table provided, match to the references in your DAX formulas.

    Are the DAX statements intended to create calculated columns or measures.

    Do you mind, to state the expected results for the calculations.

     

    Regards,

    Tom

    • rivastoday's avatar
      rivastoday
      Frequent Visitor
      7 years ago

      Hello Thanks for the support,

       

      I want to create calculated columns

       

      using this formula "NetwdHours = (CALCULATE(SUM(Calendario[Working Days]),DATESBETWEEN(Calendario[Date],FP[real_Submitted],FP[Real_Closed]))-2)*9" for the first line between the 23/03/2018 and the 02/04/2018 we have 5 working days, if I subtract 2 days I only have 3 working days. Every day has 9 hours, so if I multoply the result should be 3 days * 9 hours = 27 hours (HH:MM)

       

      Real_Submitted Real_ClosedExpected result
      23/03/2018 16:5302/04/2018 00:063 working days * 9 hours = 27 hours (HH:MM)

       

      Thanks