Creating a Prior Month Column in Power BI

[v-power]

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/2018	100.00
KPI 1	8/31/2018	99.94
KPI 1	9/30/2018	99.97
KPI 1	10/31/2018	100
KPI 1	11/30/2018	99.99
KPI 1	12/31/2018	99.86
KPI 1	1/31/2019	99.90
KPI 1	2/28/2019	100

Output like this:

KPI Name	Month	Value	Prior Month
KPI 1	7/31/2018	100.00	0
KPI 1	8/31/2018	99.94	100
KPI 1	9/30/2018	99.97	99.94
KPI 1	10/31/2018	100	99.97
KPI 1	11/30/2018	99.99	100
KPI 1	12/31/2018	99.86	99.99
KPI 1	1/31/2019	99.90	99.86
KPI 1	2/28/2019	100	99.9

 I used the following DAX query and it doesnt seem to work. Any help would be much appreciated.

Last Month = CALCULATE(FIRSTNONBLANK(Table1[Value],Table1[Value]),DATEADD(Value[Month],-1,MONTH))

[BIWorker10AW]

Is there any way we could do this in Power Query?

[v-power]

Hello @BobiRussell 

Thanks for the response. I have both of the values in same table. And "Value" is not a measure I calculated. It is part of the original table. 

[BobiRussell]

Oka V-Power,

So this is what I've used

Gross Revenue LM = CALCULATE(

[Gross Revenue], DATEADD( facts_Transactional[Invoice Date], -1, MONTH ) )

Gross Revenue LM is a measure that I added to my "Key Measures" group. My Gross Revenue is a field that I created a measure but I could have used List Revenue that is already in the tabel like yours.

My invoice date is in a different table so i had to reference the table name but it everything is in one table and want to add a column then you wont need the references the various tables.

Hope that work for you!

[v-power]

Thank you. I tried to search for the "value" field on my table1 using the calculate but it only lists the calculated measures I had created. Existing fields in the table dont show up for some reason.

[HotChilli · Accepted solution]

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]))

[v-power]

Thank you so much. This worked.

[BobiRussell]

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