Forum Discussion
Helper VNeed help to create a relationship between one to many data files.
Hi all
I am running out of ideas and I need your help please.
I'm using PowerBI Desktop to create analysis report to track every bi weekly forecast vs our budget.
While I am trying to set up a relationship between the files, I found out I do not have a column for dates that works between them.
Because there is only 2 budget data file per fiscal, one on the start of the fiscal year and one more on the mid of the fiscal year.
So for example the year 2024, my budget file date column (Report Month) will be 1/4/24 and 1/10/24.
But my Bi weekly forecast data comes every 2 weeks (twice a month) which is 15th and 28th of the month
and I need to compare every month's forecast result vs the budget.
What can I do to let Powerbi know that the budget numbers for 1/4/24 applies all the way till 9/31/24?
Currently it shows like this but I need the budget number side by side with my forecast for every month:
Apart from duplicating the budget data every 2 weeks, is there any other method I can use?
I cannot attach a sample file here and my explanation might be difficult to understand... Let me know if I should add anything to make my question easier.
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.
13 Replies
- bhanu_gautam
Super User
You can create a date table in Power BI using DAX.
DateTable =
ADDCOLUMNS (
CALENDAR (DATE(2024, 1, 1), DATE(2024, 12, 31)),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"Day", DAY([Date]),
"MonthName", FORMAT([Date], "MMMM"),
"Quarter", "Q" & FORMAT([Date], "Q")
)You can create a new table that extends the budget data to cover the necessary date ranges.
ExtendedBudget =
VAR StartDate1 = DATE(2024, 1, 4)
VAR EndDate1 = DATE(2024, 9, 30)
VAR StartDate2 = DATE(2024, 10, 1)
VAR EndDate2 = DATE(2024, 12, 31)
RETURN
UNION (
SELECTCOLUMNS (
FILTER (
CROSSJOIN (
CALENDAR ( StartDate1, EndDate1 ),
BudgetTable
),
BudgetTable[Report Month] = StartDate1
),
"Date", [Date],
"Budget", BudgetTable[Budget]
),
SELECTCOLUMNS (
FILTER (
CROSSJOIN (
CALENDAR ( StartDate2, EndDate2 ),
BudgetTable
),
BudgetTable[Report Month] = StartDate2
),
"Date", [Date],
"Budget", BudgetTable[Budget]
)
)Go to the "Model" view in Power BI.
Create a relationship between the Date column in the DateTable and the Date column in the ExtendedBudget table.
Create a relationship between the Date column in the DateTable and the Date column in the Forecast table.Create measures to calculate the budget and forecast values for comparison.
TotalBudget = SUM(ExtendedBudget[Budget])
TotalForecast = SUM(Forecast[ForecastValue])Use the DateTable as the axis in your visualizations and plot the TotalBudget and TotalForecast measures to compare the values side by side.
YuiitsuIt looks very complicated I can try,
And does this only works for year 2024?
Hi Yuiitsu ,
Thank you bhanu_gautam for the prompt response!
Upon reviewing the information provided,I tried recreate it locally.Please follow the below steps:- Use this DAX to generate a Date table:
Date =
CALENDAR(DATE(2024,1,1), DATE(2024,12,31)) - Add columns for the date table using below:
Month = FORMAT([Date], "MMMM")
YearMonth = FORMAT([Date], "YYYY-MM") - Use the below measure and please find the attached sceenshot and file for detailed understanding.
- Ensure you have the relationship between the tables.
Extended Budget Monthly =VAR SelectedDate = MAX('Date'[Date])VAR BudgetRow =FILTER('Budget Period Mapping',SelectedDate >= 'Budget Period Mapping'[Period Start] &&SelectedDate <= 'Budget Period Mapping'[Period End])VAR TotalMonths =CALCULATE(DISTINCTCOUNT('Date'[YearMonth]),FILTER(ALL('Date'),'Date'[Date] >= MAXX(BudgetRow, [Period Start]) &&'Date'[Date] <= MAXX(BudgetRow, [Period End])))VAR MonthlyBudget =DIVIDE(MAXX(BudgetRow, 'Budget Period Mapping'[Budget Value]),TotalMonths)RETURNMonthlyBudget
If the solution meets your requirement,consider accepting it as solution.
Thnak you .
Regards,
Pallavi.- Yuiitsu
Hi
I have taken a look at your sample pbix and try to make it work.
Please help with the following questions
1. Period Start and End is it additional column I must create? Because my original data does not contain this 2 column. Or rather this table Budget Period mapping where is it taken from? It is a summary from the original budget file
2. Is it possible to add more forecast value in the sample file to show how it can be view side by side?
I am currently paused at this part because I do not have the same column and not sure how it is created as well.
Apologise but please help to explain a little further.
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:
Forecast Monthly =CALCULATE(SUM(NewForeCast[Forecast Value]))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.
- Use this DAX to generate a Date table:
- Ashish_Mathur
Hi,
This can be solved using the LASTNONBLANK() function in a measure. To receive further help, share some data to work with, explain the question and show the expected result. Share data in a format that can be pasted in an MS Excel file.
- Yuiitsu
Hi Yuiitsu ,
May I ask if your issue got resolved. If a solution has been found, it would be greatly appreciated if you could share your insights with the community. This would be helpful for other members who may encounter similar issues.
Thank you for your understanding and assistance.Hi Yuiitsu ,
Can you please confirm whether you have resolved issue. If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. This will be helpful for other community members who have similar problems to solve it faster.
If we don’t hear back, we’ll go ahead and close this thread.Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.
Thank you.
