Dynamic measures based on multiple slicer selection.

[awitt]

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

[jdbuchanan71 · Accepted solution]

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

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

[awitt]

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

[jdbuchanan71]

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

[awitt]

@jdbuchanan71 

I do get an error with the LASTDATE function.

PBIX File Here

[jdbuchanan71]

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

[awitt]

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

[jdbuchanan71]

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

@awitt not sure what you are actually looking for? What is your final goal? What you are trying to achieve?

[awitt]

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.