Connecting "year" column into calendar table

[Maxaki]

Hi,

I have a calendar table which smoothly connects to other tables containing, for example, sales data with dates and time intelligence works fine here.

However, I also have a budget table containing annual budgets for different accounts. Here the information related to calendar is the year of the budgeting data. It comes as a whole number from data. Here is an example:

Year	Account number	Amount
2022	5864874	1000
2022	5864875	1200
2022	5864876	900
2021	5864874	1000
2021	5864875	1200
2021	5864876	800

...And because the year is not unique in the calendar table nor here, I cannot create a connection. 

One option would be to make an additional year table which contains nothing else but unique budgeting years, and it works as an intermediate table. But this solutions seems a bit odd to me. 

Another solution I tried is to just convert the year into DATE('data'[Year],1,1). This of course can be a bit misleading since we are adding "false information" by increasing the granularity. But also it turns out, this approach does not work, because when I try to use this info in a measure, it "cannot be used with a variation 'date' because it does not have any".

I'm pretty sure the "year only"-type columns are common in PowerBI models so what is the best practise for approaching such data. Any tips? 

[v-rzhou-msft · Accepted solution]

Hi @Maxaki ,

As far as I know, you may try to create many to many relationship between two tables directly.

Or creating a DimYear table with unique year values as you mentioned above should be a good idea.

You may refer to the offical blog as below to learn more details.

For reference:

Apply many-to-many relationships in Power BI Desktop

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.