How to find the number of hours in a StartDate and EndDate column based on the condition?

[Anonymous]

Hello 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

END_DATE identification Start_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 Sábado 13:00 18:00
XYZ123 Domingo 0:00 6:00
XYZ456 Martes 0:00 12:00

I need a Reusltant column in the first table that captures the number of hours within the Start_Date and End_Date based on the condition of 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 with the Start_Date and End_date depending on the condition is 2 Hours.

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

The condition in the second table says Saturday from 13:00 to 18:00 so the overlap is 2 hours (from 16:00 to 18:00)

Similarly, the second lasts more than one week and the overlap for the first week is 1 hour (from 5:00 to 6:00) and for the second week is 5 hours (from 0:00 to 5:00)

For the third they do not overlap so 0 hour.

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

Thank you

Nagaraj

[v-gizhi-msft · Accepted solution]

Hi,

Why does the 'ABC123' not last for more than one week?

I think according to your logic, the 2019/1/5 is the first day of first week in this case.

If so, please take following steps:

1)Create two columns:

Start_WeekNo = WEEKNUM('Table 1'[Start_Date]-4)

End_WeekNo = WEEKNUM('Table 1'[End_Date]-4)

2)Try this column:

Timeline_Hours = 
VAR a =
    TIME ( HOUR ( 'Table 1'[Start_Date] ), MINUTE ( 'Table 1'[Start_Date] ), SECOND ( 'Table 1'[Start_Date] ) )
VAR b =
    TIME ( HOUR ( 'Table 1'[End_Date] ), MINUTE ( 'Table 1'[End_Date] ), SECOND ( 'Table 1'[End_Date] ) )
VAR check_a =
    SWITCH (
        TRUE,
        a < RELATED ( 'Table 2'[EndTime] )
            && a > RELATED ( 'Table 2'[StartTime] ), DATEDIFF ( a, RELATED ( 'Table 2'[EndTime] ), HOUR ),
        a <= RELATED ( 'Table 2'[StartTime] ), DATEDIFF (
            RELATED ( 'Table 2'[StartTime] ),
            RELATED ( 'Table 2'[EndTime] ),
            HOUR
        ),
        a >= RELATED ( 'Table 2'[EndTime] ), 0
    )
VAR check_b =
    SWITCH (
        TRUE,
        b < RELATED ( 'Table 2'[EndTime] )
            && b > RELATED ( 'Table 2'[StartTime] ), DATEDIFF ( RELATED ( 'Table 2'[StartTime] ), b, HOUR ) + check_a,
        b <= RELATED ( 'Table 2'[StartTime] ), DATEDIFF (
            RELATED ( 'Table 2'[StartTime] ),
            RELATED ( 'Table 2'[EndTime] ),
            HOUR
        ),
        b >= RELATED ( 'Table 2'[EndTime] ), 0
    )
RETURN
    IF ( 'Table 1'[Start_WeekNo] = 'Table 1'[End_WeekNo], check_a, check_b )

3)The result shows:

See my attached pbix file.

Best Regards,

Giotto

[amitchandak]

@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