PowerBI creating calculation based on table

[kitala11]

 I have these 2 columns Current Month and Next Month. 

Eg. in this case I have October Next month (prediction) value of 1,692.86. And I want to use the Current Month of November 1317.91 (actual value) and calculate an accuracy value of (1692.86-1317.91)/1317.91. 
(Likewise do so for other consecutive months if possible)

Is there anyway I can do so?

[amitchandak · Accepted solution]

@kitala11 , if you have date, Try to use time intelligence and date table. Else create a month year column where you can create rank on column like YYYYMM

Using TI - measures

MTD current month = CALCULATE(SUM(Table[Current Month]),DATESMTD('Date'[Date])) // if [Current Month] is measure remove sum
last MTD next month = CALCULATE(SUM(Table[Next Month]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

Change % = divide([last MTD next month] -[MTD current month] ,[MTD current month] )


A column in date/month  table 
Month Rank = RANKX(all('Date'),'Date'[year Month],,ASC,Dense)

measures
MTD current month = CALCULATE(SUM(Table[Current Month]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
last MTD next month = CALCULATE(SUM(Table[Next Month]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

[kitala11]

Thanks for the solution. Do you know if there is any way to breakdown further into different categories as it is calculating aggregated for each whole month as of now.

[kitala11]

I don't have a date column, only have the month, although I can create YYYYMM based on that.

[amitchandak]

@kitala11 , Try based on Rank. I have discussed same in the blog too

[kitala11]

Hi using a MMM-YYYY column i created i tried the following but it didn't work.

A column in date/month  table 
Month Rank = RANKX(all('Date'),'Date'[year Month],,ASC,Dense)

measures
MTD current month = CALCULATE(SUM(Table[Current Month]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
last MTD next month = CALCULATE(SUM(Table[Next Month]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))

The 'date' here refers to the table which contains the MMM-YYYY (or YYYYMM) correct? And for table, it is referring to the table which contains the columns I have mentioned. So in my case I only have 1 table which contains all columns, the table and 'date' you are talking about is referring to the same table of mine right?