Projecting units sold for future months based on average historical growth

[Anonymous]

Hello Community!

This is my first post on this forum, so I do apologize in advance if anything is unclear. I will do my best to explain what I am trying to achieve and what I have done so far.

I am drawing data through an API that has all the information about the orders we received. This includes data such as the type of products, quantity for each product, when the order was created, total value of the order, etc.

The goal of the project that I am trying to accomplish is to project in the future how many units in total we will sell. The other projection will be what will our projected sales be. Note that both of these are different since the price for an individual product varies from customer to customer.

The projected values table must also include the breakdown of the total cost of individual raw materials we use, based on the number of units sold. I have the individual costs of the raw materials set up using measures per single product, so depending on the product and forecasted value, I plan on just multiplying the forecasted quantity by the costs to generate those sets of values corresponding to each month.

We plan on forecasting future values in 3 ways. The first is to take the historical growth of each month, average it, and use that to project into the future. The second way is to take the growth based on the last two months and project it forward. Finally, we plan on selecting an expected growth percentage and projecting it forward based on the last month's value.

The part that I am stuck on is trying to generate future values. I am using this post as a reference to accomplish this task.

To create the projection table, I used this:

Forecast Table =

var _last=MAX(OrdersReceivedLL_AllData[DateTemp])

return  ADDCOLUMNS( FILTER(CALENDAR(_last,EOMONTH(_last,12)),DAY([Date])=1),"Month Year", FORMAT([Date],"mmmm yyyy"))

The "12" bolded in the code above, I tried to insert a parameter to select how far in the future I want to forecast, but it didn't work. I made sure it's a type INT and looked for posts but to no avail. I decided to manually put 12 for now.

Forecast Table
The table above is what is generated. Now to add additional columns, what I am trying to do is the code below:

Forecasted Qty =

var _lastDate=MAXX(ALL('OrdersReceivedLL_AllData'),[DateTemp])

var _lastValue=LOOKUPVALUE(OrdersReceivedLL_AllData[line_items.quantity],'OrdersReceivedLL_AllData'[Month Year],[Month Year])

var _monthDiff= DATEDIFF(_lastDate,[Date],MONTH)

return IF(_lastValue=BLANK(), POWER(1+('Expected Growth'[Expected Growth Value]/100),_monthDiff) *SUM('OrdersReceivedLL_AllData'[line_items.quantity]), _lastValue)

The error I get is "A table of multiple values was supplied where a single value was expected."

My assumption is that the code in bold, is not monthly data, but individual lines of data. I think I probably need to figure out a way to sum the data based on the "month  year" and the look-up value should grab that. I created a column called month year in my data set, but not sure how to sum it up based on that, within the lookup function.

Below is a snippet of what I managed to accomplish by using filters and putting them on a table. Not sure how I can go about using these values automatically in DAX when there is new data coming in.

I still have some ways to go before I can successfully complete this project, but still fairly new to Power Bi and do not have anyone that I can talk to or consult. That's when I remembered that is exactly why this wonderful community is here. I am sorry that I am not sharing the data since the information is a bit sensitive and there are about 120 columns or so. I would be more than happy to get on a one-on-one if anyone is willing to assist, but of course, I don't know if anyone would want to 😅.

If you managed to read till here, I sincerely appreciate your time. Any and all assistance, tips, or resource would be greatly appreciated. Thank you very much!