Forum Discussion
PowerBI creating calculation based on table
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?
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
5 Replies
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=6LUBbvcxtKAI don't have a date column, only have the month, although I can create YYYYMM based on that.
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?
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.
