How to connect two different years' data where date is not unique and without primary key

[AmChen]

Hi Community,

I am working on 2 years data and looking to relate both of them in order to create visualiztion that can present these 2 years data's Duration include yearly, monthly, daily in one visualization through drill up and down function.

For example:

This is when both of the tables do not have relationship between each other, so 2022 data will not change at all.

Year visual:

Month visual:

Daily visual:

Howerver, I am run into problem that even I create relationship between both tables through "Date" feature for a many to many relationship, the visualization still cannot make both of the yearly data present and valid in a same visual. The visual will look like this: the 2022 data totally disappear.

Is there anyone had encountered similar situation before or have any idea?

Any suggections or directions will be really helpful, thanks!

My Data format shows in the below:

2022 data:

2023 data:

[jdbuchanan71 · Accepted solution]

You need to add a calendar table to your model and link it to both tables.

https://learn.microsoft.com/en-us/power-bi/guidance/model-date-tables

Then you can use the calendar table to summarize and compare data from both tables.

[AmChen]

Hi jdbuchanan71,

Thank you for your advise, I will definitely give it a try!

[foodd]

Hello @AmChen , and thank you for sharing a question with the Community.  This reply is informational. Please follow the decorum of the Community Forum when asking a question.

Please share your work-in-progress Power BI Desktop file (with sensitive information removed) and any source files in Excel format that fully address your issue or question in a usable format (not as a screenshot). You can upload these files to a cloud storage service such as OneDrive, Google Drive, Dropbox, or to a Github repository, and then provide the file's URL.

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.  Proud to be a Super User!

[AmChen]

Hi foodd,

Thank you for your advice. These documents are somehow tricky to edit to remove the confidential parts, so I've just uploaded a simple screenshot. But I will definitely improve my questions next time, once I figure out how to remove the confidential parts. Thanks again