Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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
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'
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
@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.
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?
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.
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |