simple calculated column and rearrange data

[adavid999]

Hi, I am (still!) just getting started w Power BI and have a question re calculated columns. I have table data like this:

Year	Question	Score
2019	I agree	50
2019	I do not agree	60
2019	I don't care	40
2018	I agree	40
2018	I do not agree	20
2018	I don't care	20

What is the best, most efficient way to get data to this (for a visual):

Question	2019	2018	Difference
I agree	50	40	10
I do not agree	60	20	40
I don't care	40	20	20

 Would appreciate any help, many thanks.

A

[amitchandak]

Create a year calendar and join with this table.
Use Year from year table in visual

this year = calculate(sum(Table[Score]), Year[Year]= max(Year[Year]))
Last year = calculate(sum(Table[Score]), Year[Year]= max(Year[Year])-1)

[adavid999]

Thanks @amitchandak . I have a couple of questions, I hope you don't mind - I am really new to this.

Is a 'year calendar' table like this?

Year

2017

2018

2019

And if so, is this then joined to my main table on 'Year'?

And then when i write your measures, do I write them exactly as you have indicated, or do I insert values(e.g. 2017, 2018 etc) for years?

Will these measures automatically subtract per the questions I included in my original post? ('I agree with this', 'I do not agree with this', etc)

Best,

Adam

[amitchandak]

Yes, the Table will be like that.

When you plot this year vs last year with the view/group by year from year table, you will able to see the switch, the name can be anything but it will not show year name unless you use the year as group by

YOY or change % = [This year]/[last year] -1

[adavid999]

Thanks @amitchandak 

I get this error when writing your measure - can you see what I am doing incorrectly?

And I don't need % year difference - just a straightforward YOY subtraction.

Best,

A

[amitchandak · Accepted solution]

Sorry my mistake

this year = calculate(sum(Table[Score]), filter(Year,Year[Year]= max(Year[Year])))
Last year = calculate(sum(Table[Score]), filter(Year,Year[Year]= max(Year[Year])-1))

divide( calculate(sum(Table[Score]), filter(Year,Year[Year]= max(Year[Year])))
, calculate(sum(Table[Score]), filter(Year,Year[Year]= max(Year[Year])-1)))-1

[adavid999]

Thanks @amitchandak , I appreciate your help.

A