Return previous month value based on two ID's

[Isildur__]

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

[Isildur__]

Thanks so much, worked perfectly!

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

[FreemanZ · Accepted solution]

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: