Forum Discussion
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.
| Category | Month | Count |
| Value1 | 1/1/2021 | 2245813 |
| Value2 | 1/1/2021 | 1417260 |
| Value3 | 1/1/2021 | 428179 |
| Value1 | 2/1/2021 | 2146907 |
| Value2 | 2/1/2021 | 1336352 |
| Value3 | 2/1/2021 | 414745 |
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?
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
2 Replies
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 - __PreviousThank 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
