Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I am using an excel file in One Drive as the dataset for a report. Is it possible to format the date in a way that it will pull in a date hierarchy instead of just the date? Right now it is pulling in 3 separate columns for the date, for Year, Month and Day but is not giving the hierarchy or the quarter option.
Hi @tsachse ,
Date hierarchy is not directly indentified when you directly pull excel to services and create report on top of that.
To add banes, you can't even create custom hierarchies, calculated columns or even group(for grouping months and creating quarter) from services.
You will have to use desktop and created calculated columns(if desktop doesn't automatically create hierarchy).
This auto date-hierarchy detection feature is actually called Time intelligence in Power BI and it can be turned on only from Desktopp options.
Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!
Hi @tsachse ,
Create a calculated column like below
date = DATE('table'[year],'table'[month],'table'[day])
Or you can add custom column in Query Editor
Date.From(Text.Combine({[year],[month],[day]},"/"))
Best Regards,
Jay
@Anonymous Are you saying to create the calculated column in the Excel file or in PowerBI? I was hoping to avoid using PowerBI desktop so that the report would update without me having to open PowerBI desktop and refresh it.
@tsachse , You can create a date column
Date =Date(Table[Year],Table[Month],Table[Date])
If you have month Like Jan or January
Date = Table[Date] & "-" & Table[Month] & "-" & Table[Year] //Change data type to date
Then create a date table and join with that.
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
Appreciate your Kudos.