Forum Discussion
How To create a datz hierachy
Hi .
As I am a first time to work in a Power BI, I develop the report in a Desktop and no Query .
I created 4 columns which of one is a DATE. Here, I would like to create a date hierachy with severals fields as year, month , etc .., without to add column manually . Because, I noticed that if I added , in a report, a table from Azure, wee see that a date hierachy is automatically created.
In addition, if I clicked on "new hierachy" , the one new fied" date hierachy" appeared without severals fields such as year, month, etc !...
Then, How to do to create a date hierachy date with severals fields as yerar, etc, automatically ... ?
Thank you in advance to help me
Sincerely
Anonymous ,
Select you date column --> Go to Modelling Tab --> Select Data Type as Date and choose any format which you want.
So automatically it will create Date Hierarchy.
Dont forget to hit Thumbs up and accept this as a solution if you find this helpful!
3 Replies
Anonymous ,
Select you date column --> Go to Modelling Tab --> Select Data Type as Date and choose any format which you want.
So automatically it will create Date Hierarchy.
Dont forget to hit Thumbs up and accept this as a solution if you find this helpful!
Date =
ADDCOLUMNS (
CALENDAR (DATE(2000;1;1); DATE(2025;12;31));
"DateAsInteger"; FORMAT ( [Date]; "YYYYMMDD" );
"Year"; YEAR ( [Date] );
"Monthnumber"; FORMAT ( [Date]; "MM" );
"YearMonthnumber"; FORMAT ( [Date]; "YYYY/MM" );
"YearMonthShort"; FORMAT ( [Date]; "YYYY/mmm" );
"MonthNameShort"; FORMAT ( [Date]; "mmm" );
"MonthNameLong"; FORMAT ( [Date]; "mmmm" );
"DayOfWeekNumber"; WEEKDAY ( [Date] );
"DayOfWeek"; FORMAT ( [Date]; "dddd" );
"DayOfWeekShort"; FORMAT ( [Date]; "ddd" );
"Quarter"; "Q" & FORMAT ( [Date]; "Q" );
"YearQuarter"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" )
)
(the first two dates on top are start and end. adjust accordingly)from here https://kohera.be/blog/power-bi/how-to-create-a-date-table-in-power-bi-in-2-simple-steps/
Anonymous
Check in the options (under "File/Options and Settings/Options/Current File/Data Load" in Power BI) to see if you have the "Time Intelligence" - "Auto date/time" checked. If it is checked, the date hierarchy is created as long as the field in the table is set to "Date/Time" in the format options.
Having said that, it is very highly recommended and a best practice (ie. everyone should do it for any solid modeling) to create a date table (which contains consecutive dates, months, month names, quarters, years, fiscal years or whatever is needed) and use this table in all measures (including time intelligence measures), visuals, slicers/filters etc. Otherwise you will have issues such as when you compare data from different periods ect. And it makes life much easier!
Personally, I leave the Time Intelligence option in the settings (shown above in the screenshot) off always.
