Forum Discussion

bobbrow's avatar
bobbrow
Microsoft Employee
4 years ago
Solved

Comparing two rows in a dataset

I have a table with some values that I want to compare across rows (specifically, I want to tell the percent difference between them by Month).  Here is a sample table with what I'm talking about.  ...
  • Greg_Deckler's avatar
    Greg_Deckler
    4 years ago

    bobbrow You could use the YEAR and MONTH functions to ensure that rows are in the same month. Basically what you have is the MTBF pattern. 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

Greg_Deckler's avatar
Greg_Deckler
Community Champion
4 years ago

bobbrow You could use the YEAR and MONTH functions to ensure that rows are in the same month. Basically what you have is the MTBF pattern. 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

bobbrow's avatar
bobbrow
Microsoft Employee
4 years ago

Thank you for the hint.  I was able to make it work using the basic pattern you shared. I benefitted from the fact that Value1 would always be the highest value so I didn't need to consider it in the formula.

 

My final result in case anyone else may benefit from it.

ConversionRate = 
  VAR __current = [Count]
  VAR __prevDate = MAXX(FILTER('Table', [Date] <= EARLIER([Date])), [Date])
  VAR __denominator = MAXX(FILTER('Table', [Date] = __prevDate), [Count])
RETURN
  [Count]*100/__denominator