Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
awitt
Helper III
Helper III

Dynamic measures based on multiple slicer selection.

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 NumberItemOrder Number ItemStatusTimeStamp
1A1AConfirmed1/1/2019
1A1AOrdered1/1/2019
1A1ADelayed1/2/2019
1A1AAllocated1/8/2019
1A1AShipped1/9/2019
1B1BConfirmed1/2/2019
1B1BOrdered1/2/2019
1B1BAllocated1/3/2019
1B1BShipped1/10/2019
2A2AConfirmed1/1/2019
2A2AOrdered1/1/2019
2A2ADelayed1/2/2019
2A2AAllocated1/8/2019
2A2AShipped1/9/2019
2C2CConfirmed1/2/2019
2C2COrdered1/2/2019
2C2CAllocated1/3/2019
2C2CShipped1/10/2019
1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

@awitt 

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)

StatusDates.jpg

I have attached my sample .pbix for you to look at.

 

View solution in original post

9 REPLIES 9
jdbuchanan71
Super User
Super User

@awitt 

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)

StatusDates.jpg

I have attached my sample .pbix for you to look at.

 

Capture.PNG

@jdbuchanan71  Thanks for the response! I am getting this error in my model though. This is just with the Date1 measure. 

 

@awitt 

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?

@jdbuchanan71 

 

I do get an error with the LASTDATE function.

 

PBIX File Here

 

@awitt 

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.

 

datediffstatus.jpg

@jdbuchanan71  That works. Is there anyway to do it with Time? Some of these statuses are only a few hours apart. 

@awitt 

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 )
parry2k
Super User
Super User

@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. 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.