Calculate Sum between two dates

[Anonymous]

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

[amitchandak · Accepted solution]

@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.

[amitchandak · Accepted solution]

@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]

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 · Accepted solution]

@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