Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

7 REPLIES 7
Shahzad
New Member

Hi,

 

As of now this is how the dataset looks;

 

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.
tschmidt87
Helper I
Helper I

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

v-yuezhe-msft
Employee
Employee

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.
ankitpatira
Community Champion
Community Champion

@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

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?

For creating the slicer you don't need to convert the slicer to date type. You can use the column in text format only for slicer.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.