How to show correct date format for drilling down

[DeBIe]

Hey all,

I am having trouble getting the correct date format when drilling down in a line chart. I have a date calendar and I created a hiearchy for Year-Quarter-Month-Day. I want it to look like this when drilling completely down:

This is my own result when drilling down from year to quarter (i'm missing the year under the quarter to show which year i'm looking at). This goes the same for drill down to month and day.

My hiearchy

Example of the date calendar

Thank you for looking into this!

[v-yalanwu-msft]

Hi, @DeBIe ;

Add another day.

Table = ADDCOLUMNS( CALENDAR(DATE(2021,12,22),TODAY()),"Year",YEAR([Date]),"Quarter","Q"&QUARTER([Date]),"Month",FORMAT([Date],"mmm"),"monthssort",MONTH([Date]),"day name",FORMAT([Date],"dddd"),"daysort",WEEKDAY([Date],2),"value",RANDBETWEEN(2,30))

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DeBIe]

Thank you very much @v-yalanwu-msft 

[DeBIe]

by the way, how to sort the day name as well?

[v-yalanwu-msft · Accepted solution]

Hi, @DeBIe ;

Try it.

1.month sort by monthsort.

2.sort visual.

3.close concatenate labels.

The final show:

If you need drill down, you could click this.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DeBIe]

Hey @v-yalanwu-msft 

Thank you so much. It works now. 

I was also using the wrong drill-down button....omg...

Thanks for your help!