Forum Discussion
Continued ContributorSorting weeks in a calendar matrix
Hello All,
I could use some help with getting the correct sort on my weeks for December and January when the year changes. In this example, December 2021 has Week 1 for 2022 at the top of the matrix but I need it at the bottom. I'm thinking I need to make that Week 53 but for 2022 it would still need to be Week 1 so that it shows correctly for January 2022.
Any ideas would be greatly appreciated!
Thanks,
I ended up going with a simplier solution. Instead of using week of year I created a new column in my date table for week of month:
WeekOfMonth = (WEEKNUM('DimDate'[date_field], 1) -WEEKNUM(DATE('DimDate'[date_field].[Year], 'DimDate'[date_field].[MonthNo], 1), 1)) +1
This worked best for me.
5 Replies
speedrampsSuper User
Use Power Query to create a calenadar table with
date, week number and start date of week
then got into modeller and sort the week number column by start date of week
if you get a error message that the sort needs to be unique then use this hack.
use the filter to just keep 1 row, then do the sort, then remove the filter
the week number will then default in the required sort sequence
If you struggle, then ask for more help and i will create an example.
Thanks for reaching out for help.
I put in a lot of effort to help you, now please quickly help me by giving kudos.
Remember we are unpaid volunteers and here to coach you with Power BI and DAX skills and techniques, not do the users job for them. So please click the thumbs up and accept as solution button.
If you give someone a fish then you only give them one meal, but if you teach them how to fish then they can feed themselves and teach others for a lifetime. I prefer to teach members on this forum techniques rather give full solutions and do their job. You can then adapt the technique for your solution, learn some DAX skills for next time and soon become a Power BI Super User like me.
One question per ticket please. If you need to extend your request then please raise a new ticket.
You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you !
nleuck_101I ended up going with a simplier solution. Instead of using week of year I created a new column in my date table for week of month:
WeekOfMonth = (WEEKNUM('DimDate'[date_field], 1) -WEEKNUM(DATE('DimDate'[date_field].[Year], 'DimDate'[date_field].[MonthNo], 1), 1)) +1
This worked best for me.- speedramps
You will get the same problem because weeks traverse months, quarters and years.
It is best prartice to sort week number by start of week date.
That solution is full proof in all scenarios. 😁
- nleuck_101
It's actually working as intended. I have 4 calendar years and it works. It resets every month.