Forum Discussion

awitt's avatar
awitt
Helper III
6 years ago
Solved

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 c...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    6 years ago

    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's avatar
awitt
Helper III
6 years ago

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

 

jdbuchanan71's avatar
jdbuchanan71
Super User
6 years ago

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's avatar
      jdbuchanan71
      Super User
      6 years ago

      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's avatar
        awitt
        Helper III
        6 years ago

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