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

Inventory forecast based on user input

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:

 

table_1.PNG

 

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

 

selector_1.PNG

 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.

 

inv_sales_2.PNG

 

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:

 

2nd_select.PNG

The table should look like this: 

 

inv_ex_21.PNG

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.

3 REPLIES 3
aj1973
Community Champion
Community Champion

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?

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

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.

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors