Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
#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!
Solved! Go to 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
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
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 |
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
thank you so much, first results show indeed the expected outcome!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |