Forum Discussion
Post PatronIncorrect year in date hierarchy
Hi vanessafvg , you're right. It looks like the date hierarchy is just adding in those extra years in the slicer even though the data doesn't actually exist.
When I filter for the incorrect years, it doesn't return any data.
Community Champion1901 is often a year used when the date value is unknown.
what table are you bringing the year from, have you done a mix and max on that table?
i would do more digging in the data.
Post PatronHi vanessafvg , Anonymous. The data comes out of SQL server as date and appears as below in Power BI,
The hierarchy is created as this:
But when I filter for year, it shows the following:
- vanessafvg
bo_afk any chance you can share your date table?
Well, in a case like this I would FIRST look at the original dataset. Does it REALLY NOT contain any day from 1901 or 1902?
Does it contains integers which might be interpreted by PBI as dates?With this poor information, I can't be of more help
- bo_afk
Hi Anonymous , vanessafvg
The original dataset does contain 1901, but that is the only valid "incorrect" year. The other years are 2019 and 2020.
In SQL server the field has data type date with the following format
Additionally, if I create a year column based on this date, it returns the correct years.
It is the date hierarchy which seems to return incorrect years
- vanessafvg
bo_afk i would think the date hiearchy which i believe is actually a virtual table will probably pad the gaps between the dates, if you want more control over the date, create your own date table and your own hiearchy from the date table, i think that might give you more control