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.

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 

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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

    Where can I find the updated sample file?

    Also looking at the screenshot it doesnt look like it matches my requirement =(

    • Anonymous's avatar
      Anonymous
      Not applicable
      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.