Price comparison over purchase orders

[iggyvic]

Hi,

I have this set of data:

That represents each item order row placed for purchase. I'd like to measure how the price has changed since the last order placed. E.g. SKU 12345 has been purchased three times: on IK11111, IK22222, and IK33333. Price increased from IK11111 to IK22222 by $0,9 but remained the same from IK22222 to IK33333. To facilitate, the order numbers are auto-generated which means a higher order number equals a later order.

I'd like to summarize all the price increases per/SKU. Furthermore, this table is keyed by means of "po-number" to a table containing dates and another one containing purchasers and I'd like to measure these summarized price increases per merchandiser and say year, quarter and month. 

Any ideas on how to structure the price difference column?

Best regards,

[v-shex-msft · Accepted solution]

Hi @iggyvic,

I'm not so sure what format of date you used in related table, can you please explain more about this?

For calculate difference between nearest id grouped by sku, you can refer to following measure formula.

Measure =
VAR currPrice =
    MAX ( Table[Price] )
VAR currID =
    MAX ( Table[Id] )
VAR prevID =
    CALCULATE (
        MAX ( Table[Id] ),
        FILTER ( ALLSELECTED ( Table ), [Id] < currID ),
        VALUES ( Table[sku] )
    )
VAR prevPrice =
    CALCULATE (
        MIN ( Table[Price] ),
        FILTER ( ALLSELECTED ( Table ), [Id] = prevID )
    )
RETURN
    currPrice - prevPrice

Regards,

Xiaoxin Sheng