How to find the Number of Hours from a StartDate and EndDate column based on the condition?

[Anonymous]

Hi Everyone,

I have two tables, First table with columns - ID, Start_Date, End_Date

                             Second table with columns - Day_of_Week, Start_Time, End_Time

ID             Start_Date                End_Date
ABC123   01/05/2019 16:00     01/07/2019 20:00
XYZ123   01/06/2019 5:00       01/13/2019 5:00
XYZ456   01/08/2019 19:00     01/13/2019 12:00
And 

ID              Day              StartTime    EndTime
ABC123     Saturday      13:00          18:00
XYZ123     Sunday         0:00            6:00
XYZ456     Tuesday        0:00           12:00

I need a Reusltant column in the first table which captures the number of hours within the Start_Date and End_Date based on the condition in the second table. In this case the result should be

ID              Start_Date     End_Date                           Timeline_Hours
ABC123    01/05/2019   16:00 01/07/2019 20:00      2
XYZ123    01/06/2019   5:00 01/13/2019 5:00          6
XYZ456    01/08/2019   19:00 01/13/2019 12:00      0

For the first record: ABC123 - Number of hours withing the Start_Date and End_date based on the condition is 2 Hours.

Reason -  Date starts from Staurday 16:00 (4PM) and ends on Monday 20:00 (8PM),

                Condition in the second table says Saturday 13:00 to 18:00 so overlap is 2 Hours ( from 16:00 to 18:00)

Similarly second one has duration of more than a week and overlap for the first week is 1 Hour (from 5:00 to 6:00) and fo the second week it is 5 Hours (from 0:00 to 5:00)

For third one no overlap so 0 Hour.

Is it possible to do in DAX or Power Query? How can I do this?

Thanks

Nagaraj

[amitchandak]

@Anonymous 

@Anonymous , Does one store open only on one day or more then one day , if it opens only on one day

We can bring time from table 2 table1

Start time Std = minx(Table2, Table1[ID] =Table2[ID]),[Start Time])
Start End Std = minx(Table2, Table1[ID] =Table2[ID]),[END Time])

Also, get from date-time

Start Time = Table[Start_Date].time

End Time = Table[End_Date].time

Then we can take  datediff(max([Start Time],[Start time Std]),Min([End Time],[End time Std]),hour)

And now we have multiply based on no of days logic

[Anonymous]

@amitchandak Sorry I did not understand the logic of your solution, how will I get the number of hours that staisfies the codition in the second table usign that method.

I need to get the amount of time which satisfies the condition in the second table from the period of start and end time!

Thanks

[amitchandak]

@Anonymous , Please find the file at:https://www.dropbox.com/s/xcrt4cw50vt6awp/betweentimearossdate.pbix?dl=0

Few time calculations need to be reworked , as I did not get the same time diff