cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BartDD Frequent Visitor
Frequent Visitor

Transform time stamp table into daily status report

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:

IDTimestampOrderIDStatus
101/12/20181Draft
205/12/20181Ongoing
310/12/20181Processed
402/12/20182Draft
507/12/20182Ongoing
604/12/20182Processed
703/12/20183Draft
803/12/20183Ongoing
908/12/20183

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/20181  
02/12/20182  
03/12/201821 
04/12/2018211
05/12/2018111
06/12/2018111
07/12/2018 21
08/12/2018 12
09/12/2018 12
10/12/2018  3

 

Thanks!

 
1 ACCEPTED SOLUTION

Accepted Solutions
v-yulgu-msft Super Contributor
Super Contributor

Re: Transform time stamp table into daily status report

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-yulgu-msft Super Contributor
Super Contributor

Re: Transform time stamp table into daily status report

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 ()
            )
    )

1.PNG

 

Then, use a Matrix to display data.

2.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BartDD Frequent Visitor
Frequent Visitor

Re: Transform time stamp table into daily status report

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:

 

IDTimestampOrderIDStatus
101/12/20181Draft
202/12/20182Draft
303/12/20183Draft
403/12/20183Ongoing
504/12/20182Processed
605/12/20181Ongoing
707/12/20182Ongoing
808/12/20183Processed
910/12/20181Processed
 
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 Super Contributor
Super Contributor

Re: Transform time stamp table into daily status report

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

BartDD Frequent Visitor
Frequent Visitor

Re: Transform time stamp table into daily status report

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 79 members 1,401 guests
Please welcome our newest community members: