Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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:
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)
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!
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.
i have a adjusted months column, the days are getting shuffled after that. don't want the week no to repeat .
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.
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
Proud to be a Super User! | |
Date tables help! Learn more
Thankyou!
But the Day column is already in whole number format.
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
Proud to be a Super User! | |
Date tables help! Learn more
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
105 | |
99 | |
99 | |
38 | |
37 |
User | Count |
---|---|
156 | |
121 | |
73 | |
73 | |
63 |