Splitting Datediff with summary table

[sovereignauto]

Good Morning, 

I have followed some of the guides on here to split dates by month.

As per the below: 

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],"Hours", (DATEDIFF(Min(FMS_UtilStatus_Changes[crimson_startdate]),Max(FMS_UtilStatus_Changes[crimson_enddate]),hour)))

But the total is wrong, the raw data shows the below: so a total of 696.68

 Start DateEnd Datecalculated Date StartCalculated date EndTime on Status (hours)

10/03/2022 13:01	22/04/2022 11:36	10/03/2022 13:01	31/03/2022 00:00	490.97
03/03/2022 11:28	09/03/2022 13:42	03/03/2022 11:28	09/03/2022 13:42	146.24
03/03/2022 11:28	03/03/2022 11:28	03/03/2022 11:28	03/03/2022 11:28	0.00
11/02/2022 16:59	03/03/2022 11:28	01/03/2022 00:00	03/03/2022 11:28	59.47

The summary table shows:

Hope the above makes sense and any help would be much appreciated

[v-yanjiang-msft · Accepted solution]

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.