Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
davidibarrag
Frequent Visitor

Using the final number of a measure to forecast.

Hello community,

I need some help from you. The thing is that I have 3 variables that I need to use to forecast some values in the future. Let me explain. I have sales, deliveries and inventory (stock), the 3 of them I have the information up to October of this year. But here comes the problem that I have, sales and deliveries I have the forecast of novembre and december, but I will create the forecast of the inventory using the last inventory date (inventory of october) + sales (november forecast) - deliveries (november forecast) = November inventory. Then I need to use the november forectast inventory to create the forecast of december, inventory date (inventory of november forecast) + sales (december forecast) - deliveries (december forecast) = december inventory. The thing is that I do not how to create a DAX formula that uses the result of november inventory (that is a measure) to create the inventory of december. I will leave a picture of an example in excel. In yellow is the information I do not have and without color is the info that I have.

davidibarrag_0-1701803580874.png

 


I appreciate if somebody can help me, thanks.

1 ACCEPTED SOLUTION

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1702177677061.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

9 REPLIES 9
davidibarrag
Frequent Visitor

Let´s say I have 3 tables, Table 1 = Inventory, Table 2 = Sales, Table 3 = Delivery. The measure for table 1 is TotalInv = count(Inventory[Order Number], the measure for Table 2 TotalSales = sum( Sales[Value] ), and the measure for table 3 is TotalDelivery = sum ( Delivery[Value] ). On the tables of sales and delivery, the data contains the forecast for the next months to come, but for the inventory the forecast is calculated using the forcast of sales and delivery. So the thing is that I want to use the last result number for the inventory measure and the forecast of sales and delivey (this data exist) to calculate the forecast of inventory. And after that, use this new number (the result of the forecast 1st month of inventory) to calculate the next one. The way to forectas the inventory is last month inventory + sales (from the month in calculation) - deliveries (from the month in calculation). I attached a photo in excel for an example. I use the number one to make the example easire to understand. Thanks @Fowmy , hopefully this explanation is better than the one above.

davidibarrag_0-1702174266259.png

 

 

Hi,

share the 3 tables in a format that can be pasted in an MS Excel file.  Ensure that there is a Date column in each table.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

davidibarrag_0-1702176041430.png

I do not know how to share so you can be able to copy paste the tables. This tables are the results of the measures for each measure. Thanks @Ashish_Mathur 

 

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1702177677061.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you for your time @Ashish_Mathur 

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
davidibarrag
Frequent Visitor

Hello @Fowmy thanks for the time, the thing is that every column in my example is a different table in Power BI with more additional information. So this solution I imagine that it would be more complicated? Or how can I make a new table to make a use of this logic for your solution?

@davidibarrag 

Understanding your data model and business logic is crucial for providing the right approach. In the scenario you've described, where each column comes from different tables with additional information, definitely, the measure that I shared should be modified.

Share a link of your data model with dummy data using Google Drive here. You can also PM me.

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Fowmy
Super User
Super User

@davidibarrag 

You need to create a new column and it should not be based on the existing inventory as it generates recursive calculation issue which is hard to solve in DAX. To achieve your desired result, you need to get deduct cumulative deliveries from cumulative sales . I added a new column to your table:
Note: Your calcualtion of invetory is incorrect, pleae check. I assume there is no opening balance here.

Actual+Forecast Inventory = 

IF(
    NOT ISBLANK( Table8[Inventory] ), 
    [Inventory],
    VAR __CumSales = 
       SUMX( FILTER( Table8 , Table8[Date] <= EARLIER( Table8[Date] ))  ,Table8[Sales] ) 
    VAR __CumDel = 
       SUMX( FILTER( Table8 , Table8[Date] <= EARLIER( Table8[Date] ))  ,Table8[Delivery] ) 
    RETURN
        __CumSales - __CumDel
)

Fowmy_0-1701805481938.png

 





 


Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.