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)
Solved! Go to 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]
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]
Great, thanks a lot ๐
User | Count |
---|---|
35 | |
31 | |
30 | |
18 | |
17 |