Forum Discussion
Difference between Multiple Columns in Matrix Presented as Rows
Hi,
I have the following matrix in powerbi with year as columns and values as rows.
Profit is a measure = Revenue - Cost
How do I calculate the profit difference between years and present them as row highlighted in yellow below. Thanks!
Hi milkywaypowerbi ,
Try this:
Profit Diff = VAR CurrentYear_ = MAX ( Sheet1[Year] ) VAR PreviousYear_ = MAXX ( FILTER ( ALL ( Sheet1[Year] ), Sheet1[Year] < CurrentYear_ ), [Year] ) VAR CurrentProfit_ = [Profit] VAR PreviousProfit_ = CALCULATE ( [Profit], FILTER ( ALL ( Sheet1 ), Sheet1[Year] = PreviousYear_ ) ) VAR MinYear_ = CALCULATE ( MIN ( Sheet1[Year] ), ALL ( Sheet1 ) ) RETURN IF ( CurrentYear_ <> MinYear_, CurrentProfit_ - PreviousProfit_ )Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi milkywaypowerbi ,
Just create another measure like so:
Profit Diff 2 = SUMX(Sheet1,[Profit Diff])Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 Replies
milkywaypowerbi , using seperate date/year table. Use year from that table in visual
This Year = CALCULATE([Profit]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE([Profit],filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])Year on column. Show on Row - True ( to show meausre on row)
Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uAHi,
Sorry I am a bit confuse by using a separate table. I created another table for year
Here is the link to the powerbi file
:https://drive.google.com/drive/folders/1_iiFNWrVGAyAhHY5hWnSbETcNGygRMYJ?usp=sharing
Can you please help me on the file.
Thank you so much!
milkywaypowerbi , whenever we create time intelligence we prefer a separate table like a date table joined with date of the table if you have data only at year level a year table joined on year.
