Forum Discussion

meskens's avatar
meskens
Frequent Visitor
4 years ago
Solved

DAX calculation with previous calculated value

Hi,

 

I have 3 different tables: 

Production forecast (year 2021-2030)

Sales forecast (year 2021-2030)

Available stock (current 2021)

 

How can I calcultate the forecast of the stock for next year with a DAX calculation?

For example: Forecast stock 2022 should be:  available stock 2021 + production forecast 2021 - sales forecast 2022

                      Forecast stock 2023 should be:  forecast stock 2022 + production forecast 2022 - sales forecast 2023

 

I've managed to calculate the first line

Forecast stock = SUM('Stock'[Quantity])+ [Production forecast last year]-[total sales forecast in year)
How can I use the calculated forecast stock value for the second line?
 

Thanks in advance,

 

Michael

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    4 years ago

    meskens Sorry, having trouble following, can you post sample data as text and expected output?
    Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

    • meskens's avatar
      meskens
      Frequent Visitor
      4 years ago

      Greg_Deckler ,

       

      Would this be of any help?

       Stock forecast 2022 would be Stock Quantity 2021 + Prod forecast Quantity 2021 - Sales Forecast Quantity 2022

      Stock forecast 2023 would be Stock forecast 2022 + Prod forecast Quantity 2022 - Sales Forecast Quantity 2023

      Table nameDateProd forecastSales forecastStock 
      Column nameYearQuantityQuantityQuantityStock Forecast (to be calculated)
       2021              4.844.683              7.697.000                24.629.251 
       2022              4.264.317              7.000.000                   22.473.934
       2023              2.208.508              7.000.000                   19.738.251
       2024                             -                7.000.000                   14.946.759
       2025              2.147.410              7.000.000                     7.946.759
    • meskens's avatar
      meskens
      Frequent Visitor
      4 years ago

      Greg_Deckler 

       

      Thank you for the answer. Then I will have to find a other way to make this happen.