Cross date time calculation

[TaroGulati]

Hi all, 

I am stuck with the following scenario:

I need to calculate time difference between (from table 1 minimum start date with category = B & type = X) and (from table 2 minimum date with category = C) so the answer will be 23 hours. Out of these 23 hours i have to check if all these 23 hours are covered in start and end date from table 1 with category = A and type = X for each ID. So in this case 22 hours out of 23 are covered so result is 22 hours. I am able to calculate the time 23 hours which is first part but not able check the scope in table 1. 

Here is link of PBIx: Sample.pbix. 

thanks

[Anonymous · Accepted solution]

Hi @TaroGulati ,

Please try:

# Measure = 
VAR __start_datetime =
    CALCULATE (
        MIN ( 'Table1'[START] ),
        'Table1'[Category] = "B"
            && 'Table1'[Type] = "X"
    )
VAR __end_datetime =
    CALCULATE ( MIN ( 'Table2'[Date] ), 'Table2'[Category] = "C" )
RETURN
SUMX(
FILTER(
    ADDCOLUMNS (
        FILTER ( 'Table1', 'Table1'[Category] = "A" && 'Table1'[Type] = "X" ),
        "@start_datetime", __start_datetime,
        "@end_datetime", __end_datetime
    ),
    'Table1'[END]>=[@start_datetime] && 'Table1'[START]<=[@end_datetime]
),
VAR __overlap_start = MAX('Table1'[START],[@start_datetime])
VAR __overlap_end = MIN('Table1'[END],[@end_datetime])
RETURN
    DATEDIFF(__overlap_start,__overlap_end,HOUR)
)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[TaroGulati]

hi, sorry didn't understand your answer. 

[lbendlin]

Please provide better sample data.

[lbendlin]

Can I interest you in a graphical solution?

Your sample data row 4 (3) is a non-event.