Forum Discussion
Understanding date hierarchy
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?
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! 🤠
1 Reply
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! 🤠