Forum Discussion
Dynamic measures based on multiple slicer selection.
- 7 years ago
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.