Forum Discussion

samdep's avatar
samdep
Icon for Advocate II rankAdvocate II
4 years ago
Solved

Creating a Flag for Stable or Reduced Rating

Hi Community!   I have a table similar to the below:   Client Assessment Rating Flag 123 Initial 2 Stable or Reduced  456 Initial 1 Stable or Reduced  123 Final 1 Stable...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    4 years ago

    samdep Seems like you need to be able to compare the current row with the previous row, that's the MTBF pattern. Although your use case is entirely different. You will need an index column or date column to define "previous".

    See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
    The basic pattern is:
    Column = 
      VAR __Current = [Value]
      VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

      VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
    RETURN
      __Current - __Previous

samdep's avatar
samdep
Icon for Advocate II rankAdvocate II
4 years ago

Greg_Deckler - Thank you, this worked for my needs! Unfortunately, while I have a date field, it isn't reliable (out of order, etc.), but your index suggestion was extremely helpful. An outright addition of an index wouldn't necessarily work since there other assessments in between these and not necessarily ordered accurately. But, I was able to assign an "index" of sorts to the the two respective assessments - and from there, the MTBF pattern works perfectly. Thank you again!!