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 |