Date Hierarchy in Service with Excel file as Dataset

[tsachse]

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.

[Anand24]

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 !!!

[Anand24]

Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!

[Anonymous]

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 

[tsachse]

@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.

[amitchandak]

@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.