cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bobbrow
Microsoft
Microsoft

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.

 

CategoryMonthCount
Value11/1/20212245813
Value21/1/20211417260
Value31/1/2021428179
Value12/1/20212146907
Value22/1/20211336352
Value32/1/2021414745

 

I want to compute the % difference between the Count of Value1 in Month 1/1/2021 with the Count of Value2 in the same month, but as a novice with DAX, I'm not sure how I can set up a filter that ensures the values I'm pulling are from the same month.  This is where I'm starting from:

 

Column =
DIVIDE(
    CALCULATE(SUM('Table'[Count]), 'Table'[Category] = "Value2"),
    CALCULATE(SUM('Table'[Count]), 'Table'[Category] = "Value1")
)

 

 

Is this possible?

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

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

 

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors