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

Difference between a date and any other dates (the minimum of those dates)

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.

NewDispositionCommisioningDelivery preparationReady for deliveryBeing deliveryPackage receivedStuff missingadditional deliveryOn Hold
01.01.202203.01.202205.01.202205.01.202205.01.202207.01.202213.01.2022   
01.01.202202.01.2022     03.01.202213.01.202208.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_newStatus_disposition Status_CommStatus_prepStatus_readyStatus_beingTotalDurationStuff_missingStatus_addStatus_OnHold
22002513   
11     5 5
 
The calculated columns should calculate the difference between the key date and the minimum date of all else.
Example (Duration Stuff missing is 5 days): I can observe on 03.01.2022 the status was changed to Stuff missing. Whats the duration of that status? It must have changed on 08.01.2022 as this is the earliest subsequent date. So 5 days duration. 
 
For column(Stuff_missing) I am thinking to calculate
1 if stuff missing < additional delivery: calculate difference 
2 if stuff missing < On hold: calculate difference 
....
9 if stuff missing < disposition: calculate difference
Return the minimum of all 9 calculated differences: Here it is according to line 2: 5 days
 
Repeat for alle other 9 status duration columns.
Thats is it. My approach.
Does anybody have an idea how to get started? If I would solve the issue for one calculated column, all other 9 calculated columns were easy. 
 
Or any other approach I should pursue? Maybe consider power automate?
 
All the best
Bob
7 REPLIES 7
tamerj1
Super User
Super User

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

1.png2.png

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

4.png

 

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. 

teflonreis
Frequent Visitor

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 

tamerj1
Super User
Super User

Hi @teflonreis 
It would be much easier if you unpivot the columns

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors