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:

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

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