Forum Discussion
Helper VNeed help to create a relationship between one to many data files.
Hi Yuiitsu ,
I understand that the last few solutions, including the one based on the sample file and screenshot provided, may not have matched your exact requirement. I truly appreciate your patience throughout this process.
I would be more than happy to continue working with you to get it resolved. On the other hand, could you please share more details about where you're facing the issue or what part isn’t working as expected? We’ll be happy to help you further.
Thank you.
Hi Yuiitsu ,
Thank you for the follow-up questions.I would be happy to help you!
1.In your original budget file, you mentioned that it only contains two entries . Since there are no explicit "Period Start" and "Period End" columns in your data, we manually create a separate table called Budget Period Mapping to define these budget periods. This table helps Power BI understand which date ranges each budget value should apply to, allowing you to spread the budget evenly across the corresponding months.
2.I have added more forecast data as requested and visualized them side by side with the monthly budget. The chart now clearly shows how each month's forecast compares with the budget.
For this, use the below query:
Please refer the screenshot and the updated file for clear understanding.
I hope this resolve your query.If so,kindly accept it as solutoion.
Thank you.
Regards,
Pallavi.
Helper VI see! Thank you for explaining.
My original data is extremely big so it might be challenging to add 2 new columns in.
In other replies I made a sample myself using a small portion of the original data, there are some explanation inside too.
Could you work your magic using the sample file I created?
Please let me know if you cannot access the box link.
Hi Yuiitsu ,
Thank again for providing the sample file.I would be happy to assist you!- Create a Date Table using following query:
DateTable =
ADDCOLUMNS (
CALENDAR (DATE(2023, 4, 1), DATE(2025, 3, 31)),
"Month", FORMAT([Date], "MMMM YYYY")
) - Create the relationship between the tables(Connect Forecast[Date] to DateTable[Date] Create a relationship: Budget[Report Month] to DateTable[Date] as inactive)
- Create a measure for dynamically selects the most recent Budget amount using following query:
Budget Filled Monthly =VAR CurrentMonth = MAX('DateTable'[MonthStart])VAR BudgetToUse =CALCULATE(MAX(Budget[Budget amount]),FILTER(Budget,Budget[Report Month] <= CurrentMonth))RETURNIF( ISINSCOPE('DateTable'[Month]), BudgetToUse ) - Add DateTable[Month] to X-axis and Forecast[Forecast Amount] and Budget Filled Monthly measure on Y axis.
Attaching the screenshot and updated sample file for your reference
If this solution meets your requirement,consider accepting it as solution.If still have any issues,feel free to reachout!
Regards,
Pallavi.- Yuiitsu
Where can I find the updated sample file?
Also looking at the screenshot it doesnt look like it matches my requirement =(
Hi Yuiitsu ,
I understand that the last few solutions, including the one based on the sample file and screenshot provided, may not have matched your exact requirement. I truly appreciate your patience throughout this process.
I would be more than happy to continue working with you to get it resolved. On the other hand, could you please share more details about where you're facing the issue or what part isn’t working as expected? We’ll be happy to help you further.
Thank you.
- Create a Date Table using following query: