Forum Discussion

TaroGulati's avatar
TaroGulati
Helper III
1 year ago
Solved

Cross date time calculation

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

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    Can I interest you in a graphical solution?

     

     

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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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