cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cong_nguyen_acc
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

5637227309FO0115/05/201915/05/2019 9:385.68USD
5637227316FO0116/05/201915/05/2019 10:025.05USD
5637227328FO0116/05/201916/05/2019 9:096.68USD
5637227329FO0116/05/201916/05/2019 9:105.68USD
5637227376FO0117/05/201917/05/2019 7:005.68USD
5637234115FO0126/06/201926/06/2019 9:425.08USD
5637234804FO0128/06/201928/06/2019 9:265.08USD
5637234805FO0128/06/201928/06/2019 9:395.08USD
5637237067FO0122/07/201922/07/2019 2:507.08USD
5637255524FO0123/10/201925/10/2019 6:0515.00SGD
6 REPLIES 6
v-zhenbw-msft
Community Support
Community Support

Hi @cong_nguyen_acc ,

 

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,

 

C 1.jpg

 

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.

amitchandak
Super User
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://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!
nandukrishnavs
Community Champion
Community Champion

@cong_nguyen_acc 

 

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)

Capture.JPG



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_IDSKU_IDInvoice_DateInvoice_DatetimeM_Previous_DateM_Min_DatePurchase_PriceCurrency_Codevariation
563716501601VCDB5TP29/05/2017 0:0030/05/2017 1:5529/05/2017 0:0029/05/2017 0:0030USD 
563717119301VCDB5TP06/09/2017 0:0011/09/2017 6:5306/09/2017 0:0006/09/2017 0:0030USD-15
563717707001VCDB5TP22/11/2017 0:0022/11/2017 9:1822/11/2017 0:0022/11/2017 0:0065USD-30
563717707101VCDB5TP22/11/2017 0:0022/11/2017 9:2022/11/2017 0:0022/11/2017 0:0065USD 
563718229901VCDB5TP29/01/2018 0:0030/01/2018 3:3929/01/2018 0:0029/01/2018 0:0065USD-189
563723263001VCDB5TP12/06/2019 0:0013/06/2019 10:3612/06/2019 0:0012/06/2019 0:0039USD-26
563725079501VCDB5TP13/09/2019 0:0013/09/2019 7:2913/09/2019 0:0013/09/2019 0:0039USD-31
563725234901VCDB5TP27/09/2019 0:0027/09/2019 7:2027/09/2019 0:0027/09/2019 0:0039USD 
563726634001VCDB5TP24/02/2020 0:0024/02/2020 10:5724/02/2020 0:0024/02/2020 0:0039USD 
563727006901VCDB5TP08/04/2020 0:0009/04/2020 8:2008/04/2020 0:0008/04/2020 0:0039USD 
AllisonKennedy
Super User
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. 

 

If you found this post helpful, please give Kudos.

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

https://sites.google.com/site/allisonkennedycv


Please @mention me in your reply if you want a response.

Check out my Olympics report with live stats - KUDOS much appreciated

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.
If you found this post helpful, please give Kudos C

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

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. 


If you found this post helpful, please give Kudos.

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

https://sites.google.com/site/allisonkennedycv


Please @mention me in your reply if you want a response.

Check out my Olympics report with live stats - KUDOS much appreciated

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.
If you found this post helpful, please give Kudos C

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

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors