Forum Discussion
Solved
Return previous month value based on two ID's
Hi Everyone, this one calculation is driving me nuts. For some context, every month a new set of records are added where the values can change or remain consistent.
Essentially I just want a calculated column that displays the previous months "value", for the same productID and CustomerID. I need this as a calculated column
My data looks like this
| ProductName/ID | CustomerID | Value | Date | Index |
| Product 1 | 55555 | A | 01/04/2022 | 1 |
| Product 1 | 55555 | B | 01/05/2022 | 50 |
| Product 1 | 55555 | B | 01/06/2022 | 100 |
| Product 2 | 55555 | 10 | 01/04/2022 | 2 |
| Product 2 | 55555 | 7 | 01/05/2022 | 51 |
| Product 1 | 66666 | 10 | 01/06/2022 | 101 |
| Product 1 | 66666 | 12 | 01/07/2022 | 151 |
Looking for a result that looks like this:
| ProductName/ID | CustomerID | Value | PM_Value | Date | Index |
| Product 1 | 55555 | A | 01/04/2022 | 1 | |
| Product 1 | 55555 | B | A | 01/05/2022 | 50 |
| Product 1 | 55555 | B | B | 01/06/2022 | 100 |
| Product 2 | 55555 | 10 | 01/04/2022 | 2 | |
| Product 2 | 55555 | 7 | 10 | 01/05/2022 | 51 |
| Product 1 | 66666 | 10 | 01/06/2022 | 101 | |
| Product 1 | 66666 | 12 | 10 | 01/07/2022 | 151 |
hi Isildur__
try to add a calculated column like this:
Value_PM2 = VAR _table = FILTER( TableName, TableName[ProductName/ID]=EARLIER(TableName[ProductName/ID]) &&TableName[CustomerID]=EARLIER(TableName[CustomerID]) ) VAR _date = MAXX( FILTER( _table, TableName[Date]<EARLIER(TableName[Date]) ), TableName[Date] ) RETURN MAXX( FILTER( _table, TableName[Date] = _date ), TableName[Value] )verified and worked like this:
3 Replies
FreemanZSuper User
hi Isildur__
try to add a calculated column like this:
Value_PM2 = VAR _table = FILTER( TableName, TableName[ProductName/ID]=EARLIER(TableName[ProductName/ID]) &&TableName[CustomerID]=EARLIER(TableName[CustomerID]) ) VAR _date = MAXX( FILTER( _table, TableName[Date]<EARLIER(TableName[Date]) ), TableName[Date] ) RETURN MAXX( FILTER( _table, TableName[Date] = _date ), TableName[Value] )verified and worked like this:
- Jihwan_Kim
Hi,
Please check the below picture and the attached pbix file.
It is for creating a new column.
PM_Value CC = VAR _prevmonthend = EOMONTH ( Data[Date], -1 ) RETURN MAXX ( FILTER ( Data, Data[ProductName/ID] = EARLIER ( Data[ProductName/ID] ) && Data[CustomerID] = EARLIER ( Data[CustomerID] ) && EOMONTH ( Data[Date], 0 ) = _prevmonthend ), Data[Value] ) Thanks so much, worked perfectly!
