There is lots of Q&A about the difference between two dates in this forum. However I have not found anything concerning this topic.
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|
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.
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 ) )
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.
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.
Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.
Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!
We had a great 2022 with a ton of feature releases to help you drive a data culture.