Forum Discussion
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/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))
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]))
7 Replies
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
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.
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!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.
