Pick Up Value from another row in the previous period and related user

[vincentia]

1. What formula will be fit to the virtual column (Sales Qn-1) so its value is picked up from the Sales (column) in previous period for the same company?

2. There will be no value for Sales Qn-1 (Virtual Column) for Q1 and Q4

3. I want to make validation for Sales (Column) > Sales Qn-1 (Virtual column)

[TeeSee1 · Accepted solution]

IF(
 MID([Period],2,1) = "1", NUMBER(""),
 INDEX(
  SELECT(
   this table[Sales],
   CONTAINS(
    [Period],
     "Q" & (NUMBER(MID([_THISROW].[Period],2,1)) - 1) & " - " & MID([_THISROW].[Period],6,4)
   )
  ),
  1
 )
)

Please adjust MID position parameters in case I mis-read your screen shot.

For the "validation" just use

[Sales] > [Sales Qn-1]

[vincentia]

Great, thanks a lot 😊