Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
@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
@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
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
83 | |
67 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |