Forum Discussion

Yuiitsu's avatar
Yuiitsu
Icon for Helper V rankHelper V
1 year ago
Solved

Need 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 ...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    1 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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
    Yuiitsu
    Icon for Helper V rankHelper V
    1 year ago

    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's avatar
      Anonymous
      Not applicable
      1 year ago

      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's avatar
        Yuiitsu
        Icon for Helper V rankHelper V
        1 year ago

        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