Tracking price movement against previous price (without time series) using DAX

[Anonymous]

Hi there,  I am looking for DAX solution to derive the previous price based on price revision sequence.  Unfortunately, I cannot use date functions as the underlying data does not hold time dimensions.  I have attached below the sample table details, the last column requires DAX formula to derive the unique price based on combination of  Product, Location and previous seq no.  Many thanks. 

Product	Location	Price Revision Seq	Current Price	Previous Price (DAX)
Coke	NewYork	1	$1.00	N/A
Coke	NewYork	2	$1.10	$1.00
Coke	NewYork	3	$1.25	$1.10
Coke	LA	1	$1.10	N/A
Coke	LA	2	$1.25	$1.10
Coke	LA	3	$1.30	$1.25
Pepsi	NewYork	1	$0.80	N/A
Pepsi	NewYork	2	$1.00	$0.80
Pepsi	NewYork	3	$1.15	$1.00
Pepsi	LA	1	$0.85	N/A
Pepsi	LA	2	$1.15	$0.85
Pepsi	LA	3	$1.20	$1.15

[amitchandak · Accepted solution]

@Anonymous 

Try like

Previous Price (DAX) = maxx(filter(table, table[Product] =earlier(table[Product] ) && table[Location] =earlier(table[Location] )
								&& table[	Price Revision Seq] =earlier(table[	Price Revision Seq] ) -1),Table[Current Price])

[Ashish_Mathur]

Hi,

Write this calculated column formula

Column = LOOKUPVALUE(Data[Current Price],Data[Product],Data[Product],Data[Location],Data[Location],Data[Price Revision Seq],CALCULATE(MAX(Data[Price Revision Seq]),FILTER(Data,Data[Product]=EARLIER(Data[Product])&&Data[Location]=EARLIER(Data[Location])&&Data[Price Revision Seq]<EARLIER(Data[Price Revision Seq]))))

Hope this helps.

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

Please create a calculated column as below.

Previous Price = 
VAR ind = 'Table'[Price Revision Seq] - 1
VAR pre =
    CALCULATE (
        MAX ( 'Table'[Current Price] ),
        FILTER (
            'Table',
            'Table'[Product] = EARLIER ( 'Table'[Product] )
                && 'Table'[Location] = EARLIER ( 'Table'[Location] )
                && 'Table'[Price Revision Seq] = ind
        )
    )
RETURN
    pre

Pbix as attached.

[amitchandak · Accepted solution]

@Anonymous 

Try like

Previous Price (DAX) = maxx(filter(table, table[Product] =earlier(table[Product] ) && table[Location] =earlier(table[Location] )
								&& table[	Price Revision Seq] =earlier(table[	Price Revision Seq] ) -1),Table[Current Price])

[Anonymous]

Thanks for prompt response and solution.  The logic works for most of the cases assuming it has like for like data values.  It does not work if any of the product is added which was not available previously.  See the example below.  Is there a way to show -482.914 for the missing product so that there is no overall price change. Many thanks in advance.

Product	Rev 1	Rev 2	DAX(Variance)	Comment
Direct - 100		482,914	482,914
None - 000	482,914		-           482,914	Not Shown
Grand Total	482,914	482,914	-