Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
KMGC
Regular Visitor

Linking 2 Tables by Year and Month; Date Table did not work

Looking for some direction on linking 2 tables by Year, Month, and Building Name. I have created a date table but that did not seem to work. I checked the 'Building Name' field to ensure that both tables match for that field. They do. 

 

The issue is that one table has a period of dates that have to be manually created. For example, a contract for a building runs from 2019-2024. If you want to compare the actual expense for that building in a certain month and a certain year, you have to convert the contract period dates to months and years so that you have comparable data. 

 

Also tried RELATED and RELATEDTABLE but neither work in order to see the contract amount and actual expense amount of a specific building in a specific month and a specific year. Dates from one table will allow you to see the month by year data from that table accurately but then the other table's data pulls everything in for that building for all months/years. 

 

Any comments would be appreciated and I hope it's just something simple that I am not seeing. Thank you in advance. 

1 REPLY 1
amalsperera
Frequent Visitor

Hi @KMGC ,

 

In the two tables create a "Start date" and "End date" using the year and month. then link them with the date table. When summing up values filter using the start date and end date. 

 

If you can post some sample data or the pbix, can give some more advice. 

 

Amal

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.