Matching Start Dates with Month numbers

[12scml]

Hello!

I'm currently working with 6 tables that carry values from various locations based on month number. So all of the tables share the same locations and share months numbered 1-100, but with different values. The issue is that month 1 corresponds to a different start date for every month (Example: Month 1 in Toronto is January 2015 while Month 1 from Vancouver is July 2016). I have a table that indexes all of these start dates based on their locations, but I don't know how to sort the data so that it can line up on one chronological date line. 

As of right now, they are all related based on these month numbers, which allows me to create graphs, but I would like to relate them with a date line rather than month number. 

Is there any way of sorting this data through Power BI without reentering the data?

[v-ljerr-msft]

Hi @12scml,

Could you post all your structures with some sample data and your expected result, so that we can better assistant on this issue? It's better that you can also share a sample pbix file if possible. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. 

Regards

[12scml · Accepted solution]

Hi! Thanks for your help but I was actually just able to get it now! I attached a date column with every table and used the "fill" command to make sure that no dates were missing. From there, I was able to establish a relationship between my tables. 

[v-ljerr-msft]

Hi @12scml,

Great to hear the problem got resolved! Could you accept your reply above as solution to close this thread? 

Regards

[Anonymous]

Generally, you are gonna want to link them all up to 1 common calendar table.   How you do that will depend a bit on if you want to aggregate the data by "everybody's week #1, whenever that was" vs "June 2016 for everyone".  You will likely need to create calculated columns on the fact tables to make them nicely "match" to your calendar strategy.