Transform time stamp table into daily status report

[BartDD]

I'm looking for a solution to transform my orders audid trail data into a table in order to show my operations performance on any give timestamp.  I have a table the gives me a timestamp and a status for each change in status of an order.  

I'm looking to be able to create a table, by date, that gives me the number of orders for each status.

My data:

ID	Timestamp	OrderID	Status
1	01/12/2018	1	Draft
2	05/12/2018	1	Ongoing
3	10/12/2018	1	Processed
4	02/12/2018	2	Draft
5	07/12/2018	2	Ongoing
6	04/12/2018	2	Processed
7	03/12/2018	3	Draft
8	03/12/2018	3	Ongoing
9	08/12/2018	3	Processed

What I would need is a new table that for all calendar days between the first and last entry in the above table counts the amount of orders for each calandar date that are in a specific status.  My statuses have a fix and logical sequence, they go from "Draft" to "Ongoing" to "Processed".   Whenever there is are multiple changes in a single day only the last status is counted.

This is what I want to achieve:

	#Draft	#Ongoing	#Processed
01/12/2018	1
02/12/2018	2
03/12/2018	2	1
04/12/2018	2	1	1
05/12/2018	1	1	1
06/12/2018	1	1	1
07/12/2018		2	1
08/12/2018		1	2
09/12/2018		1	2
10/12/2018			3

Thanks!

[v-yulgu-msft]

Hi @BartDD,

Please create a calculated table with below DAX formula:

result table =
VAR calendartable =
    CALENDAR ( MIN ( Sheet3[Timestamp] ), MAX ( Sheet3[Timestamp] ) )
VAR tempTable1 =
    SELECTCOLUMNS (
        ADDCOLUMNS (
            Sheet3,
            "startdate", Sheet3[Timestamp],
            "enddate", CALCULATE (
                MIN ( Sheet3[Timestamp] ),
                FILTER (
                    ALLEXCEPT ( Sheet3, Sheet3[OrderID] ),
                    Sheet3[ID]
                        = EARLIER ( Sheet3[ID] ) + 1
                )
            )
        ),
        "Status", [Status],
        "Start", [startdate],
        "End", [enddate]
    )
RETURN
    FILTER (
        CROSSJOIN ( calendartable, tempTable1 ),
        [Start] <= [Date]
            && (
                [End] > [Date]
                    || [End] = BLANK ()
            )
    )

Then, use a Matrix to display data.

Best regards,

Yuliana Gu

[BartDD]

Hi @v-yulgu-msft, thanks allready looking good!

It's not working yet (end date not populating) as I now see that I did not mention an additional complexity when creating the example.

My ID sequence is based on the timestamp and not on the OrderID, see data looks like this:

ID	Timestamp	OrderID	Status
1	01/12/2018	1	Draft
2	02/12/2018	2	Draft
3	03/12/2018	3	Draft
4	03/12/2018	3	Ongoing
5	04/12/2018	2	Processed
6	05/12/2018	1	Ongoing
7	07/12/2018	2	Ongoing
8	08/12/2018	3	Processed
9	10/12/2018	1	Processed

I also want to add that I have a substatus, that means status lines sometimes show multiple times in the same status.  So I need to remove them I guess to make the earlier calculation work.

Would you be able to modify to make it work?

[v-yulgu-msft · Accepted solution]

Hi @BartDD,

Please modify the formula to below:

result table =
VAR calendartable =
    CALENDAR ( MIN ( Sheet3[Timestamp] ), MAX ( Sheet3[Timestamp] ) )
VAR tempTable1 =
    SELECTCOLUMNS (
        ADDCOLUMNS (
            Sheet3,
            "startdate", Sheet3[Timestamp],
            "enddate", IF (
                Sheet3[Status] = "Draft",
                CALCULATE (
                    MIN ( Sheet3[Timestamp] ),
                    FILTER (
                        Sheet3,
                        Sheet3[OrderID] = EARLIER ( Sheet3[OrderID] )
                            && Sheet3[Status] = "Ongoing"
                    )
                ),
                IF (
                    Sheet3[Status] = "Ongoing",
                    CALCULATE (
                        MIN ( Sheet3[Timestamp] ),
                        FILTER (
                            Sheet3,
                            Sheet3[OrderID] = EARLIER ( Sheet3[OrderID] )
                                && Sheet3[Status] = "Processed"
                        )
                    )
                )
            )
        ),
        "Status", [Status],
        "Start", [startdate],
        "End", [enddate]
    )
RETURN
    FILTER (
        CROSSJOIN ( calendartable, tempTable1 ),
        [Start] <= [Date]
            && (
                [End] > [Date]
                    || [End] = BLANK ()
            )
    )

Best regards,

Yuliana Gu

[BartDD]

thank you so much, first results show indeed the expected outcome!