Forum Discussion
Need help to create a relationship between one to many data files.
- Anonymous1 year ago
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.
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.
- Yuiitsu1 year ago
Helper V
It looks very complicated I can try,
And does this only works for year 2024?