Forum Discussion
How to create Year, Month, Date hierarchy while creating reports in Power BI
Community ChampionShahzad 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.
Advocate IIHi 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.
