PowerPivot DAX: Identify last status over time per items/cases

Question

I'm new in DAX

I'm working on a IT Service Desk and I would like to have a dashboard that show me the "Running Cases" over the time.

The cases have differents status like: AWAITING - IN PROGRESS - PLANNED - CLOSED.

Cases are considering as "Running Cases" when they get status below except "CLOSED"

The goal is to have the last status for each running cases (that are not close) in order to show what is the workload

Here is the desired result: Desired Result

How to arrive to this result and what are the full data ? Here is the transaction table over 4 days and for 9 cases: Transaction tables after 4 days for each cases

I tried with these and others DAX syntaxes but I didn't get the expected result.

=COUNTROWS(FILTER(Table1;MAX(Table1[Date])))

=CALCULATE(COUNTROWS(Table1);FILTER(ALL(Table1);Table1[Date]<=MAX(Table1[Date])))

I'm having difficulties to formulaite the correct DAX syntax in order to have the desired result as below.

I would greatly appreciate your help on this matter

Hi Alejandro, Thanks for your quick answer. You right there is a mistake but not in the right place. Actually the last row of the dataset doesn't exist (08.12.16-1110-CLOSED). The last status on case 1110 is awaiting. Nobody has taken this case in charge and therefore is still running. This is the challenge of the scenario :-). We have to consider all the dates (rows) for calculation. We have 3 running cases; two on 8th of December and one on 7th of December. The measure should be able to count all running cases that are less or equal to the current date of the evaluation context — tomneo007

alejandro zuleta alejandro zuleta · Accepted Answer · 2016-12-15T18:26:52

Create a measure to get the count of rows per date where Status is different to CLOSED.

Running Cases =
CALCULATE (
    COUNTROWS ( Table1 ),
    FILTER ( Table1, Table1[Status] <> "CLOSED" )
)

Using this measure in a Pivot Table you will get this:

Note in your desired result example the running cases value for 08.12.2016 is 3 but in the sample dataset I can only count 2, hope that is a typing error.

UPDATE: Get running cases until the context date.

Create a calculated column called ClosedOn in your Table1 and use the following DAX expression.

=
IF (
    ISBLANK (
        CALCULATE (
            MIN ( [Date] ),
            FILTER (
                Table1,
                [Date] >= EARLIER ( Table1[Date] )
                    && [ID_Case] = EARLIER ( Table1[ID_Case] )
                    && [Status] = "CLOSED"
            )
        )
    ),
    MAXX ( Table1, [Date] ) + 1,
    CALCULATE (
        MIN ( [Date] ),
        FILTER (
            Table1,
            [Date] >= EARLIER ( Table1[Date] )
                && [ID_Case] = EARLIER ( Table1[ID_Case] )
                && [Status] = "CLOSED"
        )
    )
)

This calculated column will tell us when the case was closed.

Then you can use that calculated column in the running cases measure as follows:

Running Cases :=
CALCULATE (
    DISTINCTCOUNT ( Table1[ID_Case] ),
    FILTER (
        ALL ( Table1 ),
        [Date] <= MAX ( [Date] )
            && [ClosedOn] > MAX ( [Date] )
    )
)

The result in your Pivot table should be like this:

Let me know if this helps.

PowerPivot DAX: Identify last status over time per items/cases

1 Answers