Understanding date hierarchy

[silveryaroma]

Hi Forum,

I am trying to understand the logic behind the date hierarchy, and ask for your help.

In my date table, I have a Date (with a hierarchy) and a sepeare Year column that I manually created. 

However, I just noticed that if I calculate a measure using Table[Date].[Year], the year slicer should be based on Table[Date].[Year], not Table[Year]. If I use the Table[Year] variable for the slicer, the measure outcome is completely wrong. 

What is the reason?

[Daniel29195 · Accepted solution]

@silveryaroma 

when you let power bi create the date hierarchy you are using, power bi in the backend will create w table for each date column that you have in your model . 

when you use year from the hierarchy, your are using the year from the hidden table. 

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-auto-date-time

best practice : 
disable auto/ datetime  from the options , and create your year date table with the columns that you need . 

learn here how to create date able using powr query  :

https://radacad.com/all-in-one-script-to-create-date-dimension-in-power-bi-using-power-query

let me know if this works for you . 

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠