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

[ase]

Hi all,

I 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?

Kind regards,

B

[v-xiaotang · Accepted solution]

Hi @ase 

Thanks for reaching out to us.

You can try this, create a new column

Column = CALCULATE(MAX('Table'[Inventory]),FILTER(ALLEXCEPT('Table','Table'[Product]),'Table'[Month]=EARLIER('Table'[Month])+1))

Or measure,

Measure = CALCULATE(MAX('Table'[Inventory]),FILTER(ALLEXCEPT('Table','Table'[Product]),'Table'[Month]=MIN('Table'[Month])+1))

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[ase]

This seems too complicated for me. Is there a way to create a simple lookup based on the previous month value?

[Greg_Deckler]

@ase See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous