Forum Discussion
Post PartisanHow to calculate the difference between current row value and previous row value in a matrix?
Hi,
I would like to calculate the difference between the value on a matrix's current row and the value on the row below. The matix visual has three tiers - Financial Year, Financial Quarter, and Month Name.
If the matix was collapsed, so only the Financial Year (FY) was showing, then the difference would be between the current FY and the immediate row below FY.
If the matrix was expanded to the Financial Quarter (Fin Qtr) was on show then the difference would be between the current Fin Qtr and the immediate below Fin Qtr.
Finally, if the matrix was expanded to the Month Name level then the difference would be between the current line's month and the previous line's month.
The Matrix is using a Date dimension values (dimDate[Fin Yr]), [dimDate_[Fin Qtr]), and (dimDate[Month Name]). The relationship between the dimDate table and the, we'll call it, 'patent' table is dimDate[date] <<>> patent[date].
Below is the matrix visual in question.
The DAX to filful the 'Number grouped by data level' is:
Are you able to help? Thanks.
You can use visual calculations to achieve this - See the video below. Watch the intro and then skip to 7:40 where I explain about the Previous Function..
🚀 NEW FEATURE : Magic of Visual Calculations in Power BI | MiTutorials
3 Replies
miTutorialsSuper User
You can use visual calculations to achieve this - See the video below. Watch the intro and then skip to 7:40 where I explain about the Previous Function..
🚀 NEW FEATURE : Magic of Visual Calculations in Power BI | MiTutorials
- pankajnamekar25
Hello D_PBI ,
You can try this measure
Difference from Previous =
VAR CurrentPeriod = SELECTEDVALUE('dimDate'[Date])
VAR PreviousPeriod =
CALCULATE(
MAX('dimDate'[Date]),
FILTER(
ALL('dimDate'),
'dimDate'[Date] < CurrentPeriod
)
)
VAR CurrentValue = [__Patent Applications]
VAR PreviousValue =
CALCULATE(
[__Patent Applications],
'dimDate'[Date] = PreviousPeriod
)
RETURN
CurrentValue - PreviousValue
Thanks,
Pankaj Namekar | LinkedInIf this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.
D_PBImiTutorials - thanks. I came accross the Previous function for the visual calculation previously. It wasn't what I was looking for as it put the difference on the line underneather where I wanted. However, your prompt took me there again but this time, I saw Next function. This is what I wanted. See below for the differences between the two.
This visual calculation is perfect due to how the user interaction is built in the report. Thank you.
For those that are sharp eyed, the cumulative totals (yet to be tested) that may not sum the line about, could well be correct as a DISTINCTCOUNT is being used and the lesser number will be to deduction of the duplicate value.
