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

[Shahzad]

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

[Shahzad]

Hi,

As of now this is how the dataset looks;

[Anonymous]

Hi @Shahzad,

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

Thanks,
Lydia Zhang

[tschmidt87]

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

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

[Anonymous]

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

[ankitpatira]

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

[aditya_fractal]

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.

Date	Sales	Year
2020-01-01	10	2020
2020-01-02	12	2020
2019-01-04	5.9	2019

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.

Date	Sales	Year	FY
2020-01-01	10	2020	FY2020
2020-01-02	12	2020	FY2020
2019-01-04	5.9	2019	FY2019

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

Can you please help me?

[rajatanand]

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.