How do you to get accurate daily status counts for table that only logs changes?

[jdecoste]

vin	vehicle_fleet_status	vehicle_uptime_status	record_date
1G1FW6S00H4188325	In fleet	Uptime	4/26/22 0:38
1G1FW6S03H4146523	In fleet	Uptime	4/26/22 17:10
1G1FX6S00H4146123	In fleet	Uptime	4/27/22 22:39
1G1FW6S07H4183654	In fleet	Downtime	4/27/22 22:39
1G1FW6S00H4187235	In fleet	Downtime	4/27/22 22:39
1G1FZ6S05K4134756	In fleet	Uptime	4/28/22 17:25
1G1FW6S09H4181765	In fleet	Uptime	4/28/22 18:42
1G1FX6S00J4114944	In fleet	Downtime	4/28/22 19:40
1G1FX6S00J4114054	In fleet	Downtime	4/28/22 23:16

So I have a table set up like so, that logs a vehicles status. When a status changes (ie. uptime to downtime) a new row is inserted with the change reflected and the time. I am trying to get an output table that get and aggregate count of each status by day, which will look something like this.

Date	In fleet	Uptime
1/1/22	76	71
1/2/22	77	75
1/3/22	77	74

So far, I have created a measure that will count a status:

count_in_fleet = CALCULATE(COUNTROWS(vehicle_logs),vehicle_logs[vehicle_fleet_status] = "In fleet")

And a summary table that shows that by day:

vehicle_log_aggregate =

SUMMARIZE(vehicle_logs,vehicle_logs[record_date].[Date],
"In Fleet", vehicle_logs[count_in_fleet])

However, since a status may not change over one day, the counts are innacurate. I need to somehow calculate what a VIN's status will be on x date by looking at the max(status) <= x date. However, I cant figure out how to put this together. Any help is greatly appreciated!

[v-shex-msft · Accepted solution]

HI @jdecoste,

You can take a look at the following calculated table expression to create a new table with aggregated records:

Table2 =
VAR summary =
    FILTER (
        SUMMARIZE (
            ALLSELECTED ( 'Table' ),
            [Date],
            [vin],
            "Infleet",
                COUNTX (
                    FILTER ( ALLSELECTED ( 'Table' ), [Date] = EARLIER ( 'Table'[Date] ) ),
                    [vin]
                ),
            "Status Changed", COUNTROWS ( VALUES ( 'Table'[vehicle_uptime_status] ) )
        ),
        [Status Changed] >= 2
    )
RETURN
    DISTINCT (
        SELECTCOLUMNS (
            summary,
            "Date", [Date],
            "Infleet", [Infleet],
            "Status Changed", [Status Changed]
        )
    )

Regards,

Xiaoxin Sheng