Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculate Sum between two dates

Hello

 

I have 3 tables:

 

Date Table

Public Holiday Table with Dates of the holidays

Shift Table with Shift Start Date/Times and EndDate/Times, and a column with worked hours in the shift

 

I want to calculate the sum of worked hours between the Holiday date and the Holiday date - 35 days

 

Any help here would be appreciated.

 

Thanks

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , The information you have provided is not making the problem clear to me. Can you please explain with an example.
    This file has a calculation for date diff  and datediff working day. See if this can help

    https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0

     

    Also check: https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calendar-4-5-Power/ba-p/1187766
    Appreciate your Kudos.


    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hello amitchandak 

       

      Thank you for your reply

       

      I want a measure to calculate the hours worked between two dates. These dates are on another table. My measure would be something like this, but it doesn't work

       

      Hours Worked 5 Weeks from Holiday = 

      calculate([Hours worked] , Filter(ShiftTable, ShiftStartTime >= DATEADD(HolidayTableDate, -35 , DAY && ShiftStartTime <= Related(HolidayTableDate) 
       
      The relationship is ShiftTable(Date) > DateTable(Date) > HolidayTable(Date)
       
      Thank you

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        amitchandak I took your solution in the PBIX provided.

         

        I created a Min date based off the HolidayDate -35 days and a Max date based off the Holiday Date and used this formula

         

        Hours worked from H last 5 weeks = calculate([Hours worked] , Filter('ShiftTable', 'ShiftTable'[START_DATE] >= 'Holidays'[Min Date] && 'ShiftTable'[START_DATE] <= 'Holidays'[Max Date]))
         
        Thanks again