cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
meskens
Regular Visitor

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

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@meskens Yes, that helps a lot. Unfortunately, there is no clean way of doing this sort of thing in DAX as DAX does not really support previous value/recursion. Previous Value aka "Recursion" - Microsoft Power BI Community

 


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

@meskens Yes, that helps a lot. Unfortunately, there is no clean way of doing this sort of thing in DAX as DAX does not really support previous value/recursion. Previous Value aka "Recursion" - Microsoft Power BI Community

 


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

@Greg_Deckler 

 

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

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

@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

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!