Forum Discussion

ase's avatar
ase
Frequent Visitor
4 years ago
Solved

Creating a new column which takes values from another column for previous period

Hi all,   I still have a problem creating a column/measure which takes the value from another column but for the next period. Below is an illustration of the issue:       I tried to creat...
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi ase 
    Assumening the Month column is a sequential number then you may try

    New Inventory Column =
VAR CurrentMonth = TableName[Month]
VAR CurrentProductTable =
    CALCULATETABLE ( TableName, ALLEXCEPT ( TableName, TableName[Product] ) )
VAR NextMonthTable =
    FILTER ( CurrentProductTable, TableName[Month] = CurrentMonth + 1 )
RETURN
    MAXX ( NextMonthTable, TableName[Inventory] )
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Hi ase 
Assumening the Month column is a sequential number then you may try

New Inventory Column =
VAR CurrentMonth = TableName[Month]
VAR CurrentProductTable =
    CALCULATETABLE ( TableName, ALLEXCEPT ( TableName, TableName[Product] ) )
VAR NextMonthTable =
    FILTER ( CurrentProductTable, TableName[Month] = CurrentMonth + 1 )
RETURN
    MAXX ( NextMonthTable, TableName[Inventory] )
ase's avatar
ase
Frequent Visitor
4 years ago

It worked! Thank you so much for the straight-forward and easy to follow solution!