Data modeling setup for multiple fact and dimens. with forecasting in dax

[hansolu]

Hi,

I am trying to setup a data model where I do get data from different sources.
Its a big task and i guess a bit complicate.
I do have Finish goods which are build out of sub1 material. each of this sub1 material has a couple of sub2 components to make a sub1 component.

What I want to achive is:
Question 1.) Get a table with forecast per week for each finish good (think thats fine already)
Question 2.) get a table with forecast per week for each sub1item
Question 3.) get a table with forecast per week for each sub2item needed to make the finish goods in this week.
Question 4.) Compare the sub2items in each week vs. the current stock plus incoming(new arrivals of sub2item) minus required volumes to produce the FG in this week.
Question 5.) Ideally highlight the weeks where one sub2item is below saftey stock and highlight if the level is below forecast finish goods - stock - lead time to get new sub2item.


These are my fact tables:
tblWeeklyForecast: Finish good: Forecast for each week per partnumber and area
tblFGStock: stock table for each area and partnumber
tblSub1: Usage of Sub1item for each Finish part
tblSub1Stock: Stock for each Sub1item
tblSub2: Usage of Sub2items to create a sub1item, so how many sub2items in which amount i need to create 1 sub1item
tblSub2stock: stock of each sub2item at a given date
tblSub2Arrival: plan of arriving new sub2items.

These are my Dimension tabel
dFGItem: list of all Finish good numbers and name
dSubItem1: List of all Sub1Item with some more fields
dSubItem2: list of all Sub2item with lead time for purchase and required mininum stock level

My Problem:
Question 1 is fine
Question 2 is fine but the totals are wrong
Question 3 the individual number are wrong and the total is wrong.
Question 4 and 5, I do not know how to start to attack this problem


The attached workbook is having all tables and my model. I did a manual test to calculate the usage of one item in Sub2item for one week. Number do not match with my pivot result.

Overall, in terms of modeling I am a bit confused. I thought my approach with all tables seperate is good in terms of normalization. But in excel data model I am not able to achive a starscheme which would give me easy dax formulas.

I am not sure if the best way is to combine all tact tables into one big fact table. Then i can easy get my sub2items and for sub1 and finishgood i can make some dax.

So, please, anyone can give me some hints how I should proceed and how the datamodel is best setup in my constellation?

thanks a lot

[hansolu]

Hi,

I did a lot of YT videos, reading,... and come up with some, for me, nice stuff.
Question 1 to 3 is now fine, I changed the datamodel a bit and made it simpler. I also deleted teh Finish good stock and the Sub1item stock.
So the tables used in the Datamodel are different now but based on original ones.


Now Question 4 and 5 still open but I also get a step closer to it.

Remaining problem is

1: how to calculate the current sub2item per component?
I used different measures in tblStock but none of them bring me the desired result. Measure (Sub2 Stock v4) bring me closest to my wish but only returns the max of all subitem2, not the max for each single subitem2

2: Running total calculation is wrong
I calculate the weekly status with the measure(Sub2weekStatus). Now I want to sum up the values based on the selected weeks( or month or single date)

Would be great if someone can help me out on these two open questions

thanks a lot