Skip to main content
cancel
Showing results for 
Search instead 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

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/

nandukrishnavs
Super User
Super User

@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
🙂


Regards,
Nandu Krishna

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.

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

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.
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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors