How to Sort Days Properly in a Custom Date Hierarchy for a Bar Graph?

[vaishh]

I have a custom date hierarchy in my dataset which includes the following columns: Year, Adjusted Month, Week, and Day. When I use this hierarchy in a bar graph, the days are getting shuffled and are not displayed in the correct order. I need to ensure that the days are sorted properly within the context of their hierarchy levels. 

Here is the structure of my date hierarchy:

• Year
• Adjusted Month = 

  VAR CurrentDate = 'Placed Orders'[CREATED_DATE_EST]

  VAR StartOfWeek = CurrentDate - WEEKDAY(CurrentDate, 1) + 1 // Get the start of the week (Sunday)

  VAR EndOfWeek = StartOfWeek + 6 // Get the end of the week

  VAR StartOfWeekMonth = MONTH(StartOfWeek)

  VAR EndOfWeekMonth = MONTH(EndOfWeek)

  RETURN

  IF(StartOfWeekMonth = EndOfWeekMonth, StartOfWeekMonth, StartOfWeekMonth)
  

• Week
• Day

  Despite setting up this hierarchy, the bar graph doesn't show the days chronologically. What steps should I take to properly sort the days so they display correctly within this hierarchy in my bar graph? I'm using Power BI. Any help or guidance on this would be greatly appreciated!

  

[v-zhangtin-msft]

Hi, @vaishh 

You can try the following methods.

Date table:

Date = 
ADDCOLUMNS(
    CALENDAR(DATE(2023,1,1),DATE(2023,12,23)),
    "Year",YEAR([Date]),
    "Month",FORMAT([Date],"mmm"),
    "MonthNumber",MONTH([Date]),
    "Week","Week" & WEEKNUM([Date],2),
    "WeekNumber", WEEKNUM([Date],2),
    "Day", DAY([Date])
)

Is this the result you expected?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vaishh]

i have a adjusted months column, the days are getting shuffled after that. don't want the week no to repeat .

[v-zhangtin-msft]

Hi, @vaishh 

Have you solved your problem yet? If not, please describe the problem in detail and attach a screenshot.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Joe_Barry]

Hi @vaishh 

It looks like your days column is formatted as text, is this the case? If it is change the column to Whole Number

Joe

[vaishh]

Thankyou!
But the Day column is already in whole number format.

[Joe_Barry]

Are you using the date column from the Fact table? It could be that you have no data on the missing dates? If you want to show dates with no data, then I would use a Date Table (Follow the link in my bio below) Add the date related context from the Date table to the visual and the calculation measures from your Fact table to the visual. It will still not show empty dys at this point, but if you go to the build pane on the right side and right click on Year column for example choose the option "Show items with no data" and it should give you the result you want

Joe