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

[ase]

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 create a new column which was basically: Month+1 and then use looupvalue of an IF function but without any success. 

Basically I need to have for each product (Apple) the inventory in March and the inventory in April on the same line, so they can be used for visuals. 

Could you please help me out with a simple Dax to overcome this issue?

Kind regards,

B

[tamerj1 · Accepted solution]

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]

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