Need help to create a relationship between one to many data files.

[Yuiitsu]

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.

[Anonymous]

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.

[Anonymous]

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.

[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 could you see if you are able to download the sample file from this box link?

Sample pbix 

[Anonymous]

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

    )

RETURN

MonthlyBudget

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.

[Anonymous]

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.

[Yuiitsu]

I 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.

Sample pbix 

[Anonymous]

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
          )
      )
  RETURN
      IF( 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 =(

[Anonymous · Accepted solution]

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.

[bhanu_gautam]

@Yuiitsu 

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.

[Yuiitsu]

It looks very complicated I can try,

And does this only works for year 2024?