Forum Discussion

Otaki's avatar
Otaki
Frequent Visitor
4 years ago

Price recursive calculation

Hi, I have the following issue:

I have the current price and I have to calculate the final and the future prices based on  previous ones with this formula:

I've tried  this:

PriceEndTST_а =

var _max = MaxX(filter('Facts',COALESCE(Facts[InitialPrice],0)>0),Facts[year])
var _maxamt = minx(filter('Facts',Facts[year]=_max), Facts[InitialPrice]+(Facts[InitialPrice]*Facts[Increasepers]-Facts[DecreaseConst]))
var _maxMonth = maxx(filter('Facts',Facts[year]<=earlier(Facts[year])),Facts[year])
return
if(_maxMonth<=_max, ( Facts[InitialPrice]+(Facts[InitialPrice]*Facts[Increasepers]-Facts[DecreaseConst]))
,_maxamt+_maxamt*(sumx(filter(Facts, Facts[Year]>_max&& Facts[Year]<=_maxMonth), Facts[Increasepers]-Facts[DecreaseConst]/_maxamt))
but obviously  the result is incorrect:

Could you, please help?

Need Help

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    Otaki Perhaps:

    Final price column =
  VAR __Year = [Year]
  VAR __PreviousYear = __Year - 1
  VAR __PYFinalPrice = MAXX(FILTER('Facts',[Year]=__PreviousYear),[Final price])
RETURN
  __PYFinalPrice + __PYFinalPrice * [Increase Percent] - [Decrease const]
    • Otaki's avatar
      Otaki
      Frequent Visitor
      4 years ago

      This was actually my first idea however it doesn't work from the 3rd row on, as there are no values

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    Otaki check this video on my youtube channel, you need to use PRODUCTX function https://youtu.be/9Mj592bSKq0

     

     

    ✨ Follow us on LinkedIn and  to our YouTube channel

     

    Learn about conditional formatting at Microsoft Reactor

    My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

    • Otaki's avatar
      Otaki
      Frequent Visitor
      4 years ago

      unfortunately, productX doesn't  solve the issue 

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    Otaki your reply doesn't help. What you have tried? What result you are getting? Can you provide more details, If you just say, it doesn't solve the issue, it is very hard to know what you have tried so far. 

    • Otaki's avatar
      Otaki
      Frequent Visitor
      4 years ago

      Sorry, parry2k, 

      The issue is that we don't actually have a cumulative percentage on one and the same value. we have to take exactly the previous value then multiply it with the exact percentage of the period and subtract the different parameters. With productX I still couldn't get the exact results.