cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Shahzad
New Member

How to create Year, Month, Date hierarchy while creating reports in Power BI

Hi All,

 

 

I have created a report in power BI, this has seperate month and year fields in a period dimension. but i have difficulties in making a hierachy for it. eg: year 2016 month January or year 2016 month February, also to do a month on month comparison.

 

Can you tell me if their is a possibility of doing so, and how it can be done.

 

Thanks in Advance!.

 

BR,

 

Shahzad

6 REPLIES 6
ankitpatira Community Champion
Community Champion

Re: How to create Year, Month, Date hierarchy while creating reports in Power BI

@Shahzad in power bi desktop via query editor, make sure first both columns year and month are of type text. then create custom column as shown below and change type of that custom column to date. then simply use that custom column for your visuals which will give you year month hierarchy.

 

Capture.PNG

Moderator v-yuezhe-msft
Moderator

Re: How to create Year, Month, Date hierarchy while creating reports in Power BI

Hi @Shahzad,

What is the exact error message or problem you get when creating Date hierarchy?

Firstly, you don’t have to create hierarchy. You can achieve identical functionality by adding multiple columns(Year, Month)  to the "Axis" of a bar or column chart. This way, the chart will also enable you to dill down the data.

Secondly, you are able to create Date table using M query or DAX expressions in Power BI Desktop. For more detailed steps, please review the following blogs.

http://www.agilebi.com.au/power-bi-date-dimension/
http://kohera.be/blog/business-intelligence/how-to-create-a-date-table-in-power-bi-in-2-simple-steps...


Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
tschmidt87 Helper I
Helper I

Re: How to create Year, Month, Date hierarchy while creating reports in Power BI

@Shahzad

Get the HierarchySlicer from the Custom Visuals Gallery: https://app.powerbi.com/visuals/show/HierarchySlicer1458836712039

 

Add it, make sure the data field is of type "Date" and it should work as below:

 

hierarchyslicer.png

 

You can remove certain hierarchy levels, such as "Quarter" so it only lists every month of the Year below '2016'

Shahzad
New Member

Re: How to create Year, Month, Date hierarchy while creating reports in Power BI

Hi,

 

As of now this is how the dataset looks;

 

Moderator v-yuezhe-msft
Moderator

Re: How to create Year, Month, Date hierarchy while creating reports in Power BI

Hi @Shahzad,

Have you omitted the screenshot? We are not able to view the dataset you post.

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
aditya_fractal Helper I
Helper I

Re: How to create Year, Month, Date hierarchy while creating reports in Power BI

Hi @ankitpatira ,

 

I have a similar requirement where I've some sales data with the year as one of the columns. The data can be seen in the below table.

DateSalesYear
2020-01-01102020
2020-01-02122020
2019-01-045.92019

 

Now, the requirement is to create a slicer for the year such as FY2020, FY2019, etc.

Following the steps described by you, I've created a custom column i.e. FY = "FY"&[Year] but not able to change its data type to date.

DateSalesYearFY
2020-01-01102020FY2020
2020-01-02122020FY2020
2019-01-045.92019FY2019

 

Once, I change the data type to 'date', all the values in the column FY change to 'Error'.

 

Can you please help me?

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors