Inventory forecast based on user input

[FrenchConnectio]

Hi all,

I am working on an inventory forecasting model, where the user can select from the regions and stores which items they want to forecast. The data I have looks like this:

I put that data into two selectors on my dashboard, like this:

 And the user can select the items. Separately, I have data on inventory and sales levels for those items in a parent-child hierarchy, though that data is not always up to date. A sample of that data is below. Note that this is only a sample, I have inventory and sales data for every region-store-category-item combo for the dates from January 1st - January 7th.

What I am trying to output is table with a column showing the summed inventory based on the user selection. If the data ends before today, then the rest of the days should show the last reported number. For example, with those following user selection:

The table should look like this: 

The index column comes from the formula Index = Generateseries(-7,7,1) and the date is Date = Today()+Index.

My Inventory column is the sum of the inventory on that particular day for North->Alpha->Pants->Shorts + North->Alpha->Shirts->Long Sleeve. The issue I am running into is that my inventory formula is not working. I have:

Forecast_INV_2 = IF(
            AND(HASONEFILTER(Table[Region]),
                HASONEFILTER(Inventory[Category])
                ),
                    CALCULATE(
                       SUM('Table'[INVENTORY]),
                         FILTER(
                            ALL('Inventory'),
                                'Inventory'[Category]=SELECTEDVALUE(Table[Category]) && 
                                'Table'[Region] = SELECTEDVALUE(Table[Region])
                                )
                              )
                      ,0)

Once I have the inventory calculation working, then I will fill in the 0 with a formula to use the last reported number, and create another column that does the same for sales. However, I haven't been able to get this formula to work, and any advice would be appreciated.

[aj1973]

Hi @FrenchConnectio 

Your issue doesn't seem clear to many of us ! I don't see how your formula is calculating the forecast! can you share a Pibx file please?

[FrenchConnectio]

Hi @aj1973 My formula isn't actually calculating the forecast. I manually put in the numbers in the Inventory column, to demonstrate what the results should be. I can post a .pbix file with the tables I have.

[v-janeyg-msft]

Hi， @FrenchConnectio 

Only based on your description is not good to reproduce the scene to solve your problem. Can you share a sample file containing fake data? So we can help you soon.

Best Regards

Janey Guo