Forum Discussion
How do I create a relationship between months in one table and dates in another? DAX-PowerQuery
I have three data files that need to be part of the data model. DIMDATE, FORECAST, BUDGET.
BUDGET is for Fiscal Year, has multiple countries and 2 divisions, an amount for most months, sometime there is no budget for a division in one country for a month or two.
FORECAST has thousands of unique opportuinities with dates, division, country, etc.
DIMDATE has a day for each day in fiscal year, July 1, 2021 - June 30, 2022, Fiscal Month, Sort column, Day of Week, Month.
I can not create a relationship between Forecast and Budget. I was told I need to either summarize my Forecast by month OR create a budget value for every day of the year for every country and division. I have struggled to accomplish this. Any assistance would be appreciated. I can not write the DAX needed to show forecast against budget unless the there is relationship. Also I am doing this in Excel not Power BI.
Thanks,
Rob Williams
2 Replies
Hello! Here are some great resources on DAX options for budgeting/forecasting that I think will help you solve your problem.
https://www.sqlbi.com/p/dax-patterns-budget/
https://www.daxpatterns.com/budget/
https://radacad.com/budget-vs-actual-model-in-power-bi-calculations-and-dax
Good Day Rob,
Without looking at your data directly, I suggest that you try to split your monthly budget per country per division evenly by the number of days in the month to bring the Budget table to a daily "grain". For months where there is no budget for a particular division perhaps you could apply a business rule such as using last month's budget or similar. I have created a mock up in the Power BI file in the link below. I hope this helps.
Regards,
Jessica
https://drive.google.com/file/d/16Hq0OUqOvzxzm2iD3LsCtMpAlKB9_f4a/view?usp=sharing
