Search code examples
ssaspowerbidaxpowerpivotexcel-2016

Counting latest instance of multiple only based on filter context

I've got a large table of events that have occurred in an inventory of vehicles, which affect whether they are in service or out of service. I would like to create a measure that would be able to count the number of vehicles in the various inventories at any point in time, based on the events in this table.

This table is pulled from a SQL database into an Excel 2016 sheet, and I'm using PowerPivot to try to come up with the DAX measure.

Here is some example data event_list:

vehicle_id    event_date    event         event_sequence    inventory
100           2018-01-01    purchase      1                 in-service
101           2018-01-01    purchase      1                 in-service
102           2018-02-04    purchase      1                 in-service
100           2018-02-07    maintenance   2                 out-of-service
101           2018-02-14    damage        2                 out-of-service
101           2018-02-18    repaired      3                 in-service
100           2018-03-15    repaired      3                 in-service
102           2018-05-01    damage        2                 out-of-service
103           2018-06-03    purchase      1                 in-service

I'd like to be able to create a pivot table in Excel (or use CUBE functions, etc) to get an output table like this:

date          in-service     out-of-service
2018-02-04    3              0
2018-02-14    1              2
2018-03-15    3              0
2018-06-03    3              1

Essentially, I want to be able to calculate the inventory based on any date in time. The example only has a few dates, but hopefully provides enough of a picture.

I've basically come up with this so far, but it counts more vehicles than desired - I can't figure out how to only take the latest event_sequence or event_date and use that to count the inventory. 

cumulative_vehicles_at_date:=CALCULATE(
    COUNTA([vehicle_id]),
    IF(IF(HASONEVALUE (event_list[event_date]), VALUES (event_list[event_date]))>=event_list[event_date],event_list[event_date])
)

I tried using MAX() and EARLIER() functions, but they don't seem to work.

Edit: Added the PowerBI tag as I'm now using that software to attempt to solve this as well. See comments on Alexis Olson's answer.

Solution

  • I think I've found a much cleaner method than I gave previously.

    Let's add two columns onto the event_list table. One which counts vehicles "in-service" on that date and one which counts vehicles "out-of-service" on that date.

    InService = 
    VAR Summary = SUMMARIZE(
                      FILTER(event_list,
                          event_list[event_date] <= EARLIER(event_list[event_date])),
                      event_list[vehicle_id],
                      "MaxSeq", MAX(event_list[event_sequence]))

    VAR Filtered = FILTER(event_list,
                       event_list[event_sequence] =
                           MAXX(
                               FILTER(Summary,
                                   event_list[vehicle_id] = EARLIER(event_list[vehicle_id])),
                               [MaxSeq]))

    RETURN SUMX(Filtered, 1 * (event_list[inventory] = "in-service"))

    You can create an analogous calculated column for OutOfService or you can just take the total minus the InService count.

    OutOfService =
    CALCULATE(
        DISTINCTCOUNT(event_list[vehicle_id]),
        FILTER(event_list,
            event_list[event_date] <= EARLIER(event_list[event_date])))
    - event_list[InService]

    Data Table

    Now all you have to do is put event_date on the matrix visual rows section and add the InService and OutOfService columns to the values section (use Maximum or Minimum for the aggregation option rather than Sum).

    Output

    Here's the logic behind the calculated column InService:

    We first create a Summary table which calculates the maximal event_sequence value for each vehicle. (We filter the event_date to only consider dates up to the current one we are working with.)

    Now that we know what the last event_sequence value is for each vehicle, we use that to filter the entire table down to just the rows that correspond to those vehicles and sequence values. The filter goes through the table row by row and checks to see if the sequence value matches the one we calculated in the Summary table. Note that when we filter the Summary table to just the vehicle we are currently working with, we only get a single row. I'm just using MAXX to extract the [MaxSeq] value. (It's kind of like using LOOKUPVALUE, but you can't use that on a variable.)

    Now that we've filtered the table just to the most recent events for each vehicle, all we need to do is count how many of them are "in-service". I used a SUMX here where the 1*(True/False) coerces the boolean value to return 1 or 0.