Forum Discussion
2 Tables & Relationships
Hi all
I have been tasked with creating a report in Power BI which compares 2 sets of data. The report runs against the same period in this year and last year, so May 2024 - July 2024 then May 2025 - July 2025. I am using Power Query for this, running the data in full, then splitting into the relevant periods. So both lots of data have exactly the same collumns/formatting. The reason I am not leaving all data in a single table is there will be a lot more visualizations in Power BI that don't include last years figures so in theory less work needed to split the periods out.
My issue is that for a simple table that shows (Month, This Year £ and Last Year £), for Last Year £ it's only showing a total value against each month, as per screenshot. I have created a relationship between the Month Number (many to many).
Ignore the headers, it's a work in progress so titles will be corrected.
The month Number is a numeric whole number. I have also tried this with the text version, ie, May, Jun, Jul.
When the data is all in the same table this doesn't happen, so I'm sure it's a relationship issue somewhere.
I'm aware for any of these comparison reports I can create a further report in Power Query that holds these figures in one table, but trying to reason why the above doesn't work.
What am I missing? 🙂
many thanks
Hi BarryWhitelaw26,
are you using a Calendar table?
It seems that the month comes from one table of the two you created (current year and lat year)
Also, I am a bit worried about this many to many thing
I would need to take a look at the pbix, can you share it via some cloud service and paste the link here or can you
please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Do not include sensitive information and do not include anything that is unrelated to the issue or question.
Thanks
If this helped, please consider giving kudos and mark as a solution
@me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
Need help uploading data? click here
Want faster answers? click here
Problem: You're seeing repeated totals for Last Year £ because the relationship between your two tables isn't filtering correctly by month.
Fix: Create a Month dimension table and link both This Year and Last Year tables to it using one-to-many relationships. Use that dimension in your visuals to ensure proper filtering.
3 Replies
- FBergamaschi
Super User
Hi BarryWhitelaw26,
are you using a Calendar table?
It seems that the month comes from one table of the two you created (current year and lat year)
Also, I am a bit worried about this many to many thing
I would need to take a look at the pbix, can you share it via some cloud service and paste the link here or can you
please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Do not include sensitive information and do not include anything that is unrelated to the issue or question.
Thanks
If this helped, please consider giving kudos and mark as a solution
@me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
Need help uploading data? click here
Want faster answers? click here
Hi there
So in answer to your first question, no, I wasn't using a calendar table. So, I created one, and linked the 2 data tables (invoice date) via a relationship, and my table now works!
So thank you, that first question made me think about what the calendar table would do, and try it out!
Barry
- Shahid12523
Community Champion
Problem: You're seeing repeated totals for Last Year £ because the relationship between your two tables isn't filtering correctly by month.
Fix: Create a Month dimension table and link both This Year and Last Year tables to it using one-to-many relationships. Use that dimension in your visuals to ensure proper filtering.
