Using the final number of a measure to forecast.

[davidibarrag]

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.

I appreciate if somebody can help me, thanks.

[davidibarrag]

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.

[Ashish_Mathur]

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.

[davidibarrag]

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 

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[davidibarrag]

Thank you for your time @Ashish_Mathur 

[Ashish_Mathur]

You are welcome.

[davidibarrag]

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?

[Fowmy]

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

[Fowmy]

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