% difference between columns in matrix table

[dzuurman]

I have a matrix table, in which users can specify which years they want to compare the months of. Say in this case 2019 vs 2020

Of course having the totals of those months show up is easy enough. But I want to add a column in between the years showing the % difference between the two values. 

I have searched online a great deal but the answers weren't forthcoming or I may have misunderstood them.  Any help will be greatly appreciated.

[v-kelly-msft · Accepted solution]

Hi @dzuurman ，

Suppose your table is like below:

Create a measure as below:

Measure = IF(ISINSCOPE('Table'[Year]),SUM('Table'[Amount]),
var _2019=CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Year]=2019))
var _2020=CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Year]=2020))
Return
_2020-_2019)

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@dzuurman  In case you want grand total to diff of last two years.

You need to have mesure like

diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])

Then use

if(not(isinscope('Date'[Year])) && isinscope('Date'[Month]) ,[Diff %] , [This Year])

This year =SUM(Sales[Sales Amount])

Last Year = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Last Year= CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.