Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Calculate rolling Stock-on-Hand based on previous calculated quantity

Hi, I am trying to do a very simple calculation in Power BI that I have in Excel.  I would like to take this week's calculated Stock-on-Hand and use it for the next week (e.g. 1100 in week 13 for week 14 calculation). How can I achieve this in Power BI?

 

 

Thank you!

Need Help

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , we can not use a column recursively like excel. We need to think about cumulative way  

     

    example, measure

     

    Inventory / OnHand
    [Intial Inventory] + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

    Inventory / OnHand
    CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),all('Date')) + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))