supply and demand "basket / priority" problem

[Renaud]

Hi Guys,

I wanna pick your brain on this problem that I have. I am not asking for specific formulas but more of a general approach on how to do this in Power BI, because I don't see where to start.

I have on one side my needs of products by country and by priority, and on the other side my deliveries of products from the supplier at the warehouse by week. (The warehouse receive products from the supplier to deliver to the countries).

The problem is how to calculate a vision of what I can deliver to each country, for each product, and in which week.

Knowing that: the deliveries can be higher or lower than the actual needs. The unsatisfied needs will carry over to the following weeks with higher priorities

So the first delivery of products in the first week would go to the country with the highest priority, up to the need, then the remainder (if any) will go to the next country, etc, until deliveries for the first week are totally used. Any unsatisfied need is added to the next week deliveries with the highest priorities, etc.

I think I can come up with two tables, one that will represent my needs by country, priority, and products ; the other one representing the supply by week and by product to the warehouse.

I'll probably have to use some cumulative totals (deliveries up to date, total needs with higher priorities, etc.). I am really clueless on where to start.

Do you guys have any idea?

(I've done it in excel, but I can't reuse the same logic as I use sorting (by priority, by date etc.) and reference to previous row results, which PBI does not manage).

[v-yuezhe-msft]

@Renaud,

How do you store the data in Excel and what formula you use to calculate the results in Excel?

Generally, in Power BI Desktop, you can use DAX formulas to calculate the expected results after you import data from Excel.

Regards,
Lydia

[MarcelBeug · Accepted solution]

You can choose between DAX (tab "Modeling") and/or Power Query (M) (option "Edit Queries").

Both have capabilities far beyond your current perception of what might be possible in Power BI (given your statement "I can't reuse the same logic as I use sorting (by priority, by date etc.) and reference to previous row results, which PBI does not manage").

Basically PowerQuery comes first to shape unstructured data into a structured data model, next DAX steps in for further modeling (in my biased view DAX should be used for aggregation and any data operations that may lead to different results depending on any filter context).
There is a large grey area in which both Power Query and DAX can be used to acieve the same result.

In general DAX has a much broader audience (on this forum) than Power Query (M), which is actually my specialty.

Depending on your specific requirements you may use both Power Query (M) and DAX.

Just to illustrate that both are complete languages, here are the references:
Power Query (M): https://msdn.microsoft.com/en-us/library/mt779182.aspx

DAX: https://msdn.microsoft.com/en-us/library/ee634396.aspx