Splitting Datediff with summary table

Hi sovereignauto ,

According to your description, I create a sample, the result "1199" should be the datediff of the minimum startdate and the maxmum enddate in the table derived from the formula below. As the Util_summary only return one row, it regardless of the start date and end date in each row, only the maximum and minimum values ​​of the entire table are selected for comparison.

FILTER (
    CROSSJOIN ( FMS_UtilStatus_Changes, 'Date' ),
    'Date'[FullDateAlternateKey] >= ( FMS_UtilStatus_Changes[crimson_startdate].[Date] )
        && 'Date'[FullDateAlternateKey] <= ( FMS_UtilStatus_Changes[crimson_enddate].[Date] )
)

My solution is to add a 'Date'[FullDateAlternateKey] column in the summary table, modify the formula like this:

Util_summary =
SUMMARIZE (
    FILTER (
        CROSSJOIN ( FMS_UtilStatus_Changes, 'Date' ),
        'Date'[FullDateAlternateKey] >= ( FMS_UtilStatus_Changes[crimson_startdate].[Date] )
            && 'Date'[FullDateAlternateKey] <= ( FMS_UtilStatus_Changes[crimson_enddate].[Date] )
    ),
    FMS_UtilStatus_Changes[crimson_countasname],
    FMS_UtilStatus_Changes[crimson_vehicle],
    [Month-Year],
    [MonthSort],
    'Date'[FullDateAlternateKey],
    "Hours",
        (
            DATEDIFF (
                MIN ( FMS_UtilStatus_Changes[crimson_startdate] ),
                MAX ( FMS_UtilStatus_Changes[crimson_enddate] ),
                HOUR
            )
        )
)

In the visual, select all columns except FullDateAlternateKey, the Hours get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.