How do I calculate the difference between rows of ££, per column ?

[RobMP]

Hi all, I'm a couple of weeks into using Power BI now. Great tool, a little overwhelming at first.

I'm trying to work out how to get the % difference between ££ values per row, per column.

Here's the data I'm working on, so I want to work out the difference from one month to the next, per column (so each column has it's own % difference to show)

Date	Div1	Div2	Div3	Div4	Div5	Div6	Div7	Div8
01/01/2020 00:00	£22,440.00	£5,876.00	£36,886.00	£6,845.00	£845.00	£43,996.00	£2,239.00	£711.00
01/12/2019 00:00	£21,825.00	£5,873.00	£35,815.00	£6,844.00	£845.00	£42,171.00	£2,239.00	£711.00
01/11/2019 00:00	£22,132.00	£5,872.00	£35,256.00	£6,844.00	£845.00	£40,837.00	£1,989.00	£709.00
01/10/2019 00:00	£23,788.00	£4,126.00	£33,640.00	£6,860.00	£845.00	£38,627.00	£1,989.00	£709.00
01/09/2019 00:00	£24,050.00	£3,453.00	£32,935.00	£6,846.00	£845.00	£36,572.00	£1,989.00	£709.00
01/07/2019 00:00	£24,307.00	£3,453.00	£30,738.00	£6,843.00	£845.00	£35,669.00	£1,989.00	£707.00
01/06/2019 00:00	£22,333.00	£3,264.00	£28,785.00	£6,845.00	£845.00	£34,686.00	£1,988.00	£707.00

Any ideas how I might achieve that please?

Thanks.

[Anonymous]

Date	Div1	Diff Div1	Div2	Diff Div2
01/01/2020 00:00	£22,440.00	--	£5,876.00
01/12/2019 00:00	£21,825.00	-2.8%	£5,873.00	-0%
01/11/2019 00:00	£22,132.00	+1.4%	£5,872.00	-0%
01/10/2019 00:00	£23,788.00	...	£4,126.00	..
01/09/2019 00:00	£24,050.00	...	£3,453.00	..
01/07/2019 00:00	£24,307.00	...	£3,453.00	...
01/06/2019 00:00	£22,333.00	...	£3,264.00	..

Something like this?

[RobMP]

Hi, yes that's the result I'm after. Should I do that as a calculated column, or a measure? (if so, how please?)

Thanks.

[Anonymous · Accepted solution]

You can do both depending on the model, but typically that's a measure, a time intelligence one.

Do you have a date dimension?

If not, please create it (look it up on the web, there are plenty of resources if you look for "how to create a date dim in powerbi")

Once you have it created, you need a measure that basically does your value calcuation

Amount=SUMX(Table;Table[amount]) 
which basically calculate your amount (your value in the cell at the interesection of month and div)

Then do a previous month calculation
AmountPreviousMonth=CALCULATE([Amount];PREVIOUSMONTH(DateTable[DateKey])

This will calculate the same amount but on previous month.

Then do the percentage in a new measure

p.s. I write pseudocode, so correct syntax if needed.

[RobMP]

Perfect, thanks!