Forum Discussion

Isildur__'s avatar
Isildur__
Frequent Visitor
3 years ago
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 ca...
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    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:

FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
3 years ago

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: