Forum Discussion
Solved
Creating a Prior Month Column in Power BI
Hello All, I have a table like below. I would like to create another column for Prior Month which would display previous months value as a column. KPI Name Month Value KPI 1 7/31/2...
Hi,
I'm sure there are a few solutions to this.
If we use DATEADD in a measure like
Prev Month Sale = CALCULATE(SUM(Table1[Value]), DATEADD(Table1[Month],-1,MONTH))The measure will return some correct values but some blanks.DATEADD won't always work in this example because the dates it works out have to exist in the table. Let's take one case from the table - '30 sept 2018', subtract 1 month from this and we get '30 Aug 2018' (and that date doesn't exist in the table)
What we need is a measure like this :
Prev Month Sale 2 = CALCULATE(SUM(Table1[Value]), PREVIOUSMONTH(Table1[Month]))
Hi v-power
See if this works.
Table Name = should refer to the table that you have the revenue or in your case "Value". The "Table Name[Month]" is your date table either in your facts table or Calendar table. This work for my reporting and should work?
Prior Month = CALCULATE(Table Name[Value], DATEADD(Table Name[Month], -1, MONTH ) )
Best,
Bobi