Difference between Multiple Columns in Matrix Presented as Rows

[milkywaypowerbi]

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!

[Icey · Accepted solution]

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.

[amitchandak]

@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_0uA

[milkywaypowerbi]

Hi,

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!

[Icey · Accepted solution]

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.

[milkywaypowerbi]

Hi,

Thank you so much!

However, the total column is not displaying right total difference. I would like a sum of the difference. Can you assist with that?

[Icey · Accepted solution]

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.

[milkywaypowerbi]

Thank you so much!

[amitchandak]

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

[milkywaypowerbi]

Hi @amitchandak 

I have created another table for the year but the codes are different from my naming so I am getting errors since I am unsure is it referring to the year in revenue sheet or date

Can you please assist with my table names? Thanks!