Latest Enterprise DNA Initiatives

Forecast Model convert annual data to week and month

Data Setup
#1

Hi,

I am trying to create the report from raw data so it is a bit difficult to visualize the correct tables.
I have 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 relationships.
Thanks,
NupurDemand Forecast Sample.pbix (1.6 MB)

#2

Hi @Nupur, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.
  • When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.
    image
  • Use the proper category that best describes your topic
  • Provide as much context to a question as possible.
  • Include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

#3

Hi @Nupur -

  1. Please explain the data and requirements in bit more detail to help further. In Forecast table, I can see only one Date Column with only 3 dates.
  • How you want to be split into Weekly and Monthly numbers.
  • Is table LedgerPeriodAllocation need to be involved. If yes, then how.
  1. Relationship b/w week and Line Number will be Many to Many only as same week number will be present for different Financial Years. To make relation 1-Many, you may need to merge financial year with Week Number in both Date table and LedgerPeriodAllocation.

Thanks
Ankit J

#4

Hi @ankit,

The Demand Forecast table has the forecast at an annual level. It has the forecast at an item level and each item code has a Period Key associated with it. We just need to consider one financial year (2021) here for the report.
The LedgerPeriodAllocationCategoryStaging table has the Period Key and the allocation percentage for each key on a 7-day interval as per the time interval column.
I want it to be split based on a monthly level.
We need to create a link between the demand forecast and the ledgerperiod allocation so that based on the period key it splits the annual numbers to weekly/monthly forecast.
Please let me know if there is any further confusion and I can clear it.
I just need a suggestion on how best tp go ahead with the modelling so we get the correct outcome.

Thanks,
Nupur

#5

Hi @Nupur! We noticed that your inquiry was left unsolved for quite some time now. We strive to answer ALL inquiries in the forum. However, if you are sure that you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, you can check out tutorials to learn this yourself as your membership also comes with relevant resources that may help you with your Power BI education.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!

#6

Hi @EnterpriseDNA,

Ok. Can you please suggest the tutorials and resources that can help me guide me through this issue.
It will be much appreciated.

Thanks,
Nupur

#7

Hello @Nupur. We have a thread with some recommendations from other Power BI users. Hope this helps.