Year column cannot be used in visuals

[TSchmidt]

Hi there!

I have been banging my head into the wall regarding a semantic model i have build on a lakehouse and hope you might have suggestions to my problem.

My model contains the following tables:

My calendar table has been marked as a date table and has the following relationship:

I have currently created a simple measure COUNTROWS(equipmentmanager) and want to create a table visual showing how many leases ends each year. However i receive the following error:

When i try with other columns from my calendar table everything works fine e.g. month:

Do you have any suggestions to why it is only the year column i cannot use in my visuals?

Best regards

Troels

[v-mdharahman]

Hi @TSchmidt,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you are trying to get data on how many leases ends each year. Can you provide some sample data so that it will be easier to understand your issue and give a complete solution to your query.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[TSchmidt]

Hi @v-mdharahman ,

Thank you for the message!

Please find sample data from both tables below:
equipmentmanager

Name(Text)	EquipmentCategory(Text)	Supplier(Text)	Department(Text)	EndOfLease(Date)
Equipment#1	Category#1	Supplier#2	Department#1	11-10-2026
Equipment#2	Category#1	Supplier#3	Department#1	02-04-2026
Equipment#3	Category#2	Supplier#2	Department#4	02-04-2026

calendar

Date(Date)	MonthNum(Wholenum)	Month(Text)	Year(Wholenum)
01-01-2010	01	Jan	2010
...
31-12-2050	12	Dec	2050

Let me know if further clarification of the model or data is needed.

I have another observation i would like to share.

When using the aggregate of the year column for my visual it works fine:

However when i select "Don't summarize" it gives me the error:

Looking forward hearing from you

Best regards

Troels

[pallavi_r · Accepted solution]

Hi @TSchmidt ,

Create a date hierarchy, it will solve the problem. Please refer.

https://community.fabric.microsoft.com/t5/Desktop/Fiscal-Date-Hierarchies-Mark-as-Date-Table/m-p/810...

https://www.youtube.com/watch?v=1Y0T_KkeWyQ

If this post helps, please accept this as a solution. Appreciate your kudos.

Thanks, 

Pallavi