Create a link between two databases and calculate forecast per week

Hi all,

I have two databases:

  • One database has demand forecast at annual level with the period allocation keys. The allocation is at a product family level. So, there are multiple item codes with the same key

  • the Second database has period allocation keys with the allocation percentage per week.

I also have a Time entity table. I am trying to create a demand forecast at a weekly level based on the allocation percentages in the period key. But I am unable to link the two tables as the relationship between the period allocation keys is many to many.

Can someone help me figure out a way to create the relationship between these so I can have a demand forecast per item at a weekly level?

Attached the sample file.Demand Forecast Sample.pbix (1.6 MB)

Thanks,
Nupur

Hi @Nupur,

Here are some initial thoughts on the supplied PBIX

  1. Your Date table wasn’t marked as a date table and you were leveraging of the hidden date table in a calculated column.
  2. There was no relationship between Product and Forecast, as a consequence you hadn’t used the Product Item code in your table visual but the Forecast Item code. I strongly suggest hiding all keys on the Fact table side to avoid these kind of selection errors.
  3. Your Date dimension table doesn’t have sufficient detail for what you are trying to achieve. For example it doesn’t include a single dimension at the Week level, that you intent to report on…
  4. I suspect you want to use the Timeinterval column as your Week ID within the allocation table but as mentioned above, currently there are no dimensions at the week level present in your date table. You need at least one to match this.
    Note that there’s no Year ID tied to the Timeinterval so without an active filter on the Year all Week IDs across all selected years could be seen as valid.

When a common dimension for Weeks is introduced/present in both Date- and Allocation table, you should create a relationship virtually with TREATAS because the granularity in the Date table should be at the daily level while in the Allocation table it’s at the weekly level. You will find many excellent resources within both the portal and the forum on how to do just that.

But first, before you write any DAX, your model needs to be set up correctly, please review the Advanced data transformations and modelling course within the portal.

I hope this is helpful.

3 Likes

Hi @Nupur, we’ve noticed that no response has been received from you since the 30th of November. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

A response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!

Hi @Melissa,

Thank you for the detailed explanation. I am trying to create the report from raw data so it is a bit difficult to visualize the correct tables.
I have made the changes in the report and attached the updated sample version.

Can you please be able to guide me through the next steps to create the proper connection and get the forecast per item at the annual level to be split into weekly and monthly numbers?
Also, I tried to create a link between the Week in the Financial Year column in the Date table and the Line Number column in LedgerPeriodAllocation table but it is still many to many relationshipsDemand Forecast Sample.pbix (1.6 MB) .

Thanks again for your help on this.
Nupur

We’ve noticed that you posted on an inactive and previously tagged solved topic. For more visibility please start a new topic within the forum. You may check this how-to guide for reference - How To Use The Enterprise DNA Support Forum https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951