Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi there
There is lots of Q&A about the difference between two dates in this forum. However I have not found anything concerning this topic.
Given:
For a small-scale delivery process in PowerBI. The columns are of type date. And describe a status the order may go through.
New | Disposition | Commisioning | Delivery preparation | Ready for delivery | Being delivery | Package received | Stuff missing | additional delivery | On Hold |
01.01.2022 | 03.01.2022 | 05.01.2022 | 05.01.2022 | 05.01.2022 | 07.01.2022 | 13.01.2022 | |||
01.01.2022 | 02.01.2022 | 03.01.2022 | 13.01.2022 | 08.01.2022 |
Looking for:
I would like to calculate the duration of each status. I am trying it using calculated columns.
In addition to the above table, I would therefore add the following calculated columns.
Status_new | Status_disposition | Status_Comm | Status_prep | Status_ready | Status_being | TotalDuration | Stuff_missing | Status_add | Status_OnHold |
2 | 2 | 0 | 0 | 2 | 5 | 13 | |||
1 | 1 | 5 | 5 |
Hi @teflonreis
To be honest, I don't fully understand the requirement. However, I believe the following should be a good start. First step is to unpivot the table using PQ
Then the calculated column would be
New Days =
VAR CurrentNewDate = Unpivotted[New]
VAR CurrentNewDateTable = CALCULATETABLE ( Unpivotted, ALLEXCEPT ( Unpivotted, Unpivotted[New] ) )
VAR CurrentValueDate = Unpivotted[Value]
VAR MinValueDate = MINX ( CurrentNewDateTable, Unpivotted[Value] )
RETURN
IF ( CurrentValueDate = MinValueDate, DATEDIFF ( CurrentNewDate, CurrentValueDate, DAY ) )
Whoa
First of all Many many thanks for taking great time and effort to help me. Really surprises me how active our community is. I will definitely adopt your approach to the mastertable, where there are proper IDs to track everything in the unpivoted table. Will keep you updated about progress.
Thanks for the tip. Will use unpivoted columns. Hopefully I get an idea.
If there are any other tips, I am open and thankful for any comments. Still figuring out.
@teflonreis
How does your source data looks like? Can you share a sample along with the expected results?
Hey I have a sample BI file attached here.
Rows represent an order that has been made.
Columns the dates of status change.
Expected results: DurationNew for third row should be the #days between New and Preparation. But in fifth row, DurationNew is the #days between New and StartedDelivery.
For me, DurationNew is defined how long it stays New before changed to any other status. Since there are many statuses, I would use the minimum of those dates.
In row 3, 23.Mai.2022 (Preparation) is the minimum date of all others. Hence: DurationNew for third row should be the #days between New and Preparation.
Maybe there is a more efficient approach. Using PowerBI models, a second table called calender and linking those as a model. Then a master list with time stamps of each change.
Or I take it back to the source which is a sharepoint list. And use Power Automate to save the durations of each status.
@teflonreis
Great.
Unfortunately I already left the office. I will have a look at it early morning tomorrow
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |