Price Difference using Power M or DAX

[ThisIsFalse]

I have a report that is to calculate the price diference between two products as a fucntion of time, To do this I have written a function in Power M.  It takes a record looks at the date and product selects all the records after that date orders them, then keeps the last two (which it does for each row) and subtract them to obtain the price difference. I was wondering if there is a better way to do this in DAX, which I am not that familar with.  I feel Like Mesures are the way to go with this since  the calculation would only be preformed if you look at that data, but  I don't know how to get around the row logic in my sql method.  

This is my Power M fucntion: 

let
    
    PriceDiff = (Div as number, Product as number, RowDate as date, TargetTable as table, Shipto as text, RowPrice as number) as any=>
let
   Source = if Table.IsEmpty(Table.SelectRows(TargetTable, each ([DIV] = Div and [PROD] = Product and [MinDateRSV] < RowDate and [SHIPTO] = Shipto))) then
        Table.FromRecords({[DIV = 0, PROD = 0, SALES_EA = 0, MinDateRSV = 0]})
     else
         Table.SelectRows(TargetTable, each ([DIV] = Div and [PROD] = Product and [MinDateRSV] < RowDate and [SHIPTO] = Shipto)),

    #"Sorted Rows" = Table.Sort(Source,{{"MinDateRSV", Order.Descending}}),
    #"Select 1 row"= Table.Range(#"Sorted Rows", 0,1),

    #"Subtract Unit Prices" = if #"Select 1 row"{0}[SALES_EA] <> 0 then
        RowPrice - #"Select 1 row"{0}[SALES_EA] 
    else 
        0            
in
    #"Subtract Unit Prices" 
in 
    PriceDiff

The problem I am having is that the queries use an unreasonable amount of time and system resources. 

I am a bit of a noob so please don't go overboard on how much this code sucks. 

In any event I appreciate any comments and time spent helping me out. Sample Output

[ThisIsFalse · Accepted solution]

It took me a while but what I ended up doing was using an index after sorting by by date in the SQL (Power M) side, this allowed me reference rows in the Dax satament. The over all idea is that I shift the prices down one so that the privious dates can be compared to the current ones. This is the Code I ended up using in the DAX side (This is a Calculated column).

Raw Price Difference = if(AND(MasterARBHold[CustomerName] = LOOKUPVALUE(MasterARBHold[CustomerName],MasterARBHold[Index],
MasterARBHold[Index]-1),AND(MasterARBHold[PROD] = LOOKUPVALUE(MasterARBHold[PROD],MasterARBHold[Index],MasterARBHold[Index]-1),
AND(MasterARBHold[SHIPTO] = LOOKUPVALUE(MasterARBHold[SHIPTO],MasterARBHold[Index],MasterARBHold[Index]-1),
MasterARBHold[MinDateRSV] > LOOKUPVALUE(MasterARBHold[MinDateRSV],MasterARBHold[Index],MasterARBHold[Index]-1)))),
LOOKUPVALUE(MasterARBHold[SALES_EA],MasterARBHold[Index],MasterARBHold[Index]-1),MasterARBHold[SALES_EA])

The basic logic is that the current row is "selected" by making sure all the key values are the same for the product, then it returns privious price by finding the next lower index that matches the product, or if it it does not find a matching Product it simply returns the current price. This is a simplified version of the code:

Raw Price Difference = if((ProductTable[Product] = LOOKUPVALUE(ProductTable[Product],
ProductTable[Index],ProductTable[Index]-1),LOOKUPVALUE(ProductTable[SALES_EA],
ProductTable[Index],ProductTable[Index]-1),ProductTable[SALES_EA])

I then used a another calcuated column that subtracts the sales each from the price that is "shifted" one place down. Where the prices changed I will have a value, otherwise it will be 0.

for completeness:

PriceDiff = MasterARBHold[SALES_EA]-MasterARBHold[Raw Price Difference]

I did end up finding out how to do this online so I can't claim to have come up with this method on my own, however at the moment I do not remember the place where to point to for atribution. Sorry about that!
Thanks for those that took the time to respond.

[v-xjiin-msft]

Hi @ThisIsFalse,

=> I have a report that is to calculate the price diference between two products as a fucntion of time

Yes, you can achieve this with DAX expression. Since we cannot see your actual situation, it is hard for us to provide a specific suggestion. So please share us more detailed information like some orginal sample data which we can copy and paste directly and its corresponding expected result.

Thanks,
Xi Jin.

[ThisIsFalse]

This is a bit tricky but here is some raw data that I would use (it is put in order a bit but this should be representative) I would use a group() in Power M to reduce the number of rows but I am not sure exactly how to do that in DAX ( I have tried to use it but can't figure out how to get it right).

DIV	RSV_DATE	CUSTOMER	CNM	                AMOUNT	PROD    PROD_DESC	               SALES_EA	QTY   SHIP_CITY SHIP_DAT	SNM	               SHST	INDUSTRY SHIPTO
72	3/29/2018	5	ADVANCED PAPER ENTERPRISE	5033.6	1002	48 X 36 4W 3R 7/0 3/0 PALLET	9.68	520	RIPON	3/28/2018	ADVANCED PAPER ENTER	WI	IND	00000005*001
72	3/20/2018	5	ADVANCED PAPER ENTERPRISE	4778.8	1002	48 X 36 4W 3R 7/0 3/0 PALLET	9.19	520	RIPON	3/15/2018	ADVANCED PAPER ENTER	WI	IND	00000005*001
72	2/20/2018	5	ADVANCED PAPER ENTERPRISE	4778.8	1002	48 X 36 4W 3R 7/0 3/0 PALLET	9.19	520	RIPON	2/19/2018	ADVANCED PAPER ENTER	WI	IND	00000005*001
72	2/7/2018	5	ADVANCED PAPER ENTERPRISE	3859.8	1002	48 X 36 4W 3R 7/0 3/0 PALLET	9.19	420	RIPON	2/6/2018	ADVANCED PAPER ENTER	WI	IND	00000005*001
72	2/7/2018	5	ADVANCED PAPER ENTERPRISE	1108	1003	48 X 40 4W 3R 2/5 2/3 PALLET	11.08	100	RIPON	2/6/2018	ADVANCED PAPER ENTER	WI	IND	00000005*001

[v-xjiin-msft]

Hi @ThisIsFalse,

There's a lot of ways to do group work within DAX, like SUMMARIZE() and so on. It depends on your requirement.

Since you want to get the price difference. What's the logic to do the calculation? According to your shared sample data, what's your expected result?

Thanks,
Xi Jin.