Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I am looking to calculate a time difference between two status of the same order with the status changing as I need. The idea is that I would have two identical slicers with each of the statuses to chose from. So in this case "Confirmed" "Ordered" "Delayed" "Allocated" and "Shipped". In slicer 1 I choose "Confirmed" and the other I choose "Shipped" and the result is either an average or some other time between function.
I may want to choose different time between statuses though. So i may want to look at time between Allocated and Shipped and so on. Right now i have dozens of statuses and I have to create a measure for each expression I want to evaluate. This can be literally hundreds of measures... Thoughts?
Order Number | Item | Order Number Item | Status | TimeStamp |
1 | A | 1A | Confirmed | 1/1/2019 |
1 | A | 1A | Ordered | 1/1/2019 |
1 | A | 1A | Delayed | 1/2/2019 |
1 | A | 1A | Allocated | 1/8/2019 |
1 | A | 1A | Shipped | 1/9/2019 |
1 | B | 1B | Confirmed | 1/2/2019 |
1 | B | 1B | Ordered | 1/2/2019 |
1 | B | 1B | Allocated | 1/3/2019 |
1 | B | 1B | Shipped | 1/10/2019 |
2 | A | 2A | Confirmed | 1/1/2019 |
2 | A | 2A | Ordered | 1/1/2019 |
2 | A | 2A | Delayed | 1/2/2019 |
2 | A | 2A | Allocated | 1/8/2019 |
2 | A | 2A | Shipped | 1/9/2019 |
2 | C | 2C | Confirmed | 1/2/2019 |
2 | C | 2C | Ordered | 1/2/2019 |
2 | C | 2C | Allocated | 1/3/2019 |
2 | C | 2C | Shipped | 1/10/2019 |
Solved! Go to Solution.
I took a pass at a solution but I have a question. Right now I am showing the highest date so if you are looking at order 1 ordered date it will show 1/2 but if you look at order 1 with the item it will have a different date for the two items.
First we make a couple of tables using all available status. Doint it this way insures we always have the full list.
Status1 = DISTINCT('Table'[Status])
Status2 = DISTINCT('Table'[Status])
I have a couple measures to test the dates that are coming from the slicers (Date1 and Date2)
And finally a meaure to do the calc.
DateDiff = VAR FirstStatus = SELECTEDVALUE ( Status1[Status] ) VAR FirstStatusDate = CALCULATE(LASTDATE('Table'[TimeStamp]),'Table'[Status] = FirstStatus ) VAR SecondStatus = SELECTEDVALUE ( Status2[Status] ) VAR SecondStatusDate = CALCULATE(LASTDATE('Table'[TimeStamp]),'Table'[Status] = SecondStatus ) RETURN DATEDIFF( FirstStatusDate,SecondStatusDate,DAY)
I have attached my sample .pbix for you to look at.
I took a pass at a solution but I have a question. Right now I am showing the highest date so if you are looking at order 1 ordered date it will show 1/2 but if you look at order 1 with the item it will have a different date for the two items.
First we make a couple of tables using all available status. Doint it this way insures we always have the full list.
Status1 = DISTINCT('Table'[Status])
Status2 = DISTINCT('Table'[Status])
I have a couple measures to test the dates that are coming from the slicers (Date1 and Date2)
And finally a meaure to do the calc.
DateDiff = VAR FirstStatus = SELECTEDVALUE ( Status1[Status] ) VAR FirstStatusDate = CALCULATE(LASTDATE('Table'[TimeStamp]),'Table'[Status] = FirstStatus ) VAR SecondStatus = SELECTEDVALUE ( Status2[Status] ) VAR SecondStatusDate = CALCULATE(LASTDATE('Table'[TimeStamp]),'Table'[Status] = SecondStatus ) RETURN DATEDIFF( FirstStatusDate,SecondStatusDate,DAY)
I have attached my sample .pbix for you to look at.
@jdbuchanan71 Thanks for the response! I am getting this error in my model though. This is just with the Date1 measure.
I don't know why you are getting that error. This measure works for me and there are duplicate dates in the table.
LastDate = LASTDATE ( 'Table'[TimeStamp] )
Do you get the error with this measure? If so, can you share your .pbix file?
It's because you have a time in the field. If you add a column that has just the date.
Date = DATE ( YEAR ( OrderItemStatusHistory[Datetime] ), MONTH ( OrderItemStatusHistory[Datetime] ), DAY ( OrderItemStatusHistory[Datetime] ) )
You can do the measure over that column and it will work.
@jdbuchanan71 That works. Is there anyway to do it with Time? Some of these statuses are only a few hours apart.
Yes, it can. If we use MAX instead of LASTDATE it will keep the time and we don't need the additional date column.
Date1 = VAR FirstStatus = SELECTEDVALUE ( StatusTable1[Status] ) RETURN CALCULATE ( MAX ( OrderItemStatusHistory[Datetime] ), OrderItemStatusHistory[Status] = FirstStatus )
DateDiff = VAR FirstStatus = SELECTEDVALUE ( StatusTable1[Status] ) VAR FirstStatusDate = CALCULATE( MAX ( OrderItemStatusHistory[Datetime] ), OrderItemStatusHistory[Status] = FirstStatus ) VAR SecondStatus = SELECTEDVALUE ( StatusTable2[Status] ) VAR SecondStatusDate = CALCULATE( MAX ( OrderItemStatusHistory[Datetime] ), OrderItemStatusHistory[Status] = SecondStatus ) RETURN DATEDIFF( FirstStatusDate,SecondStatusDate,DAY )
@awitt not sure what you are actually looking for? What is your final goal? What you are trying to achieve?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Pretty much what the other commenter got at. Having two seperate slicers containing a large selection of statuses and building measures based on the dynamic selection of those two slicers. So in this example its a DateDiff function based off the two slicers - the difference in time between any selection of statuses. I might need an average or a count of how many times something happened moving forward.
The issue is building a model that has a dynamic selection of statuses rather than writing a measure for each one.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
91 | |
86 | |
76 | |
66 |
User | Count |
---|---|
149 | |
117 | |
111 | |
106 | |
95 |