cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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.

+ An item mays have many currency codes.

+ the dates aren't continuous.

the goal is calculation the price variance with previous price , correspoding with currency code.

Pls help me with this.

Thanks & best regards,

Record_ID SKU_ID Invoice_Date Invoice_Datetime Purchase_Price Currency_Code

 5637227309 FO01 15/05/2019 15/05/2019 9:38 5.68 USD 5637227316 FO01 16/05/2019 15/05/2019 10:02 5.05 USD 5637227328 FO01 16/05/2019 16/05/2019 9:09 6.68 USD 5637227329 FO01 16/05/2019 16/05/2019 9:10 5.68 USD 5637227376 FO01 17/05/2019 17/05/2019 7:00 5.68 USD 5637234115 FO01 26/06/2019 26/06/2019 9:42 5.08 USD 5637234804 FO01 28/06/2019 28/06/2019 9:26 5.08 USD 5637234805 FO01 28/06/2019 28/06/2019 9:39 5.08 USD 5637237067 FO01 22/07/2019 22/07/2019 2:50 7.08 USD 5637255524 FO01 23/10/2019 25/10/2019 6:05 15 SGD
6 REPLIES 6
Community Support

We test the nandukrishnavs’ code and it works fine.

Maybe you can try the following measure.

Measure =
VAR current_time =
MIN ( 'Table'[Invoice_Datetime] )
VAR _previousDateTime =
CALCULATE (
MAX ( 'Table'[Invoice_Datetime] ),
ALLSELECTED ( 'Table' ),
'Table'[SKU_ID] IN DISTINCT ( 'Table'[SKU_ID] ),
'Table'[Invoice_Datetime] < current_time,
'Table'[Currency_Code] IN DISTINCT ( 'Table'[Currency_Code] )
)
VAR _previousPrice =
CALCULATE (
SUM ( 'Table'[Purchase_Price] ),
ALLSELECTED ( 'Table' ),
'Table'[Invoice_Datetime] = _previousDateTime
)
RETURN
IF (
ISBLANK ( _previousPrice ),
BLANK (),
SUM ( 'Table'[Purchase_Price] ) - _previousPrice
)

The result like this,

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User

@cong_nguyen_acc , You can get last date price like. Use date table

A new measure

Last Day Non Continous Price = CALCULATE(Avg('Table'[Price]),filter(all('Date'),'Date'[Date] =MAXX(FILTER(all('Date'),'Date'[Date]<max('Date'[Date])),Table['Date'])))

And take diff with price

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Super User

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)

Appreciate with a kudos
🙂

Regards,
Nandu Krishna

Frequent Visitor

Thanks

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
Super User

If you are wanting the price it was sold at most recently, you'll need to use EARLIER function inside a calculated column. If you want the price from a previous day or month, then you need to create a continuous date table, relate it to your existing table and use time intelligence.

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Super User

Further to my previous reply, here is a sample formula that should help:

Previous Purchase Price = MAXX(FILTER(Items, Items[SKU_ID]=EARLIER(Items[SKU_ID])&&Items[Currency_Code]=EARLIER(Items[Currency_Code])&& Items[Invoice_DateTime]<EARLIER(Items[Invoice_DateTime])),Items[Purchase_Price])

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.