Previous order without using "date" field

[Anonymous]

Hi!

The task is for the user to select a year on the slicer and get two results:

• The cost of orders in the selected year (this is not a problem).
• And the cost of previous orders.

I can't use a "date" to find a previous order - one customer may have several different products that can't be mixed, and related orders are not necessarily in neighboring years. But we have a unique serial number for each order and information about the serial number of the previous order, through which they can be linked.

Choosing 2018 in slicer, we should get:

• Amount of current orders is 261.
• Amount of previous orders is 250.

It is better to do this through a "measure" rather than a "calculated column".

Example table:

https://drive.google.com/file/d/1p7LSf7rBcJcTG_zG4E4g_k_0sjXT_Qfz/view?usp=sharing 

[Anonymous]

Hi Alex,

I have a solution that works (on the sample set):

I have used 3 measures. The one for previous order is just to show which order is selected as previous.

Previous Order = 
var CurrentOrder = Max(orders[Serial])
return CALCULATE(max(orders[Serial]), filter( all(Orders), Orders[Serial]<CurrentOrder))

Previous Price = 
var CurrentOrder = Max(orders[Serial])
var CurrentClient = Max(Orders[Client])
var CurrentProduct = Max(Orders[Product])
var PreviousOrder = CALCULATE(max(orders[Serial]), filter( all(Orders), Orders[Serial]<CurrentOrder))
var PreviousPrice = LOOKUPVALUE('Orders'[Price], Orders[Serial] , PreviousOrder, Orders[Client], CurrentClient, Orders[Product], CurrentProduct)
var Result = PreviousPrice
return 
Result

Previous Price 2 = 
if (HASONEVALUE(Orders[Serial]), [Previous Price], sumx(ALLSELECTED(Orders), [Previous Price]))

Previous Price 2 is what calculates the correct total. (I did not bother too much about the looks of the table as long as the result is correct 🙂 )

I have the feeling that there a more elegant solutions available, but this is how far my expertise brings me, hope it helps. 

Jan