Forum Discussion
Calculation price variance over time
Hi, I need help solving a calculation like this : + The table below shows the purchase price of an item from times to times. + A date mays have many prices , distincted by increased record_ID. ...
Variation =
var _currencu='Table'[Currency_Code]
var _previousDateTime=MAXX(FILTER(ALL('Table'),'Table'[SKU_ID]=EARLIER('Table'[SKU_ID])&&'Table'[Invoice_Datetime ]<EARLIER('Table'[Invoice_Datetime ])&&'Table'[Currency_Code]=_currencu),'Table'[Invoice_Datetime ])
var _previousPrice= MAXX(FILTER(ALL('Table'),'Table'[Invoice_Datetime ]=_previousDateTime),'Table'[Purchase_Price ])
var _variation= 'Table'[Purchase_Price ]-_previousPrice
return IF(ISBLANK(_previousPrice),BLANK(),_variation)
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂
Thanks nandukrishnavs,
Your code works but it still have glitch :
Variation goes wrong when i select this item:
+ Line 5637171193 , the expected result should be 0 .
+ Line 5637177070 should be 35.
+ Line 5637182299 should be 0.
+ Line 5637250795 should be 0.
Pls check you code with this data sample.
Thanks.
Record_ID SKU_ID Invoice_Date Invoice_Datetime M_Previous_Date M_Min_Date Purchase_Price Currency_Code variation 5637165016 01VCDB5TP 29/05/2017 0:00 30/05/2017 1:55 29/05/2017 0:00 29/05/2017 0:00 30 USD 5637171193 01VCDB5TP 06/09/2017 0:00 11/09/2017 6:53 06/09/2017 0:00 06/09/2017 0:00 30 USD -15 5637177070 01VCDB5TP 22/11/2017 0:00 22/11/2017 9:18 22/11/2017 0:00 22/11/2017 0:00 65 USD -30 5637177071 01VCDB5TP 22/11/2017 0:00 22/11/2017 9:20 22/11/2017 0:00 22/11/2017 0:00 65 USD 5637182299 01VCDB5TP 29/01/2018 0:00 30/01/2018 3:39 29/01/2018 0:00 29/01/2018 0:00 65 USD -189 5637232630 01VCDB5TP 12/06/2019 0:00 13/06/2019 10:36 12/06/2019 0:00 12/06/2019 0:00 39 USD -26 5637250795 01VCDB5TP 13/09/2019 0:00 13/09/2019 7:29 13/09/2019 0:00 13/09/2019 0:00 39 USD -31 5637252349 01VCDB5TP 27/09/2019 0:00 27/09/2019 7:20 27/09/2019 0:00 27/09/2019 0:00 39 USD 5637266340 01VCDB5TP 24/02/2020 0:00 24/02/2020 10:57 24/02/2020 0:00 24/02/2020 0:00 39 USD 5637270069 01VCDB5TP 08/04/2020 0:00 09/04/2020 8:20 08/04/2020 0:00 08/04/2020 0:00 39 USD
