Forum Discussion

cong_nguyen_acc's avatar
cong_nguyen_acc
Frequent Visitor
6 years ago

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
Need Help

6 Replies

  • nandukrishnavs's avatar
    nandukrishnavs
    Community Champion
    6 years ago

    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)



    Did I answer your question? Mark my post as a solution!
    Appreciate with a kudos     🙂

    • cong_nguyen_acc's avatar
      cong_nguyen_acc
      Frequent Visitor
      6 years ago

      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 
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Community Support
    6 years ago

    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,

     

     

    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.

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    6 years ago

    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

    • AllisonKennedy's avatar
      AllisonKennedy
      Community Champion
      6 years ago

      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

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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/