How do I create a relationship between months in one table and dates in another? DAX-PowerQuery

[rowilli]

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

[JessicaD]

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 

[jennratten]

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