Chronological Week Number Order in Matrix Over Multiple Years

[Anonymous]

Hello,

I'm trying to get my "Week Num and Week Start Date" column in my Matrix Visual to order correctly from left to right(i.e...W52 12/22, W53 12/29, W2 01/05, W3 01/12, etc.). However, the chronological order is broken once I refresh the data to bring in January data.

This is what my Matrix looks like before the January data is brought in. Everything looks good.

After I refresh the dataset and bring in January data, then the ordering of the Week Nums is incorrect. I want the Week Nums to continue on the right-hand side after "W53 12/29".

I added in the Calendar Year column from my Date Dim to the Matrix which helps a little but then "W53 12/29" is split between the Years 2019 and 2020 as Week 53 overlaps over both Years.

Is there a way to handle this particular scenario? I would rather leave out the Year from my Matrix Visual but I'm ok with incorporating the Year column in my Matrix to get the correct Week Number order. I would then need "W53 12/29" for the Year 2020 to fall before "W2 01/05" but after "W53 12/29" for the Year 2019 instead of where it's located right now after "W4 01/19".

[mpsAutomation]

How did you managed to add the Calendar Year to avobe the weeks on the matrix, I know is unrelated but it will solve my issue

[amitchandak · Accepted solution]

If you can use week year.

create a week's year. For that get week strat date as a new column and have week year as another

Week Start date = DATEADD('Date'[Date],-1*WEEKDAY('Date'[Date])+1,DAY)
Week Year =year('Date'[Week Start date])

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[IlMoro]

Hi, I have the same situation however I did not understand what I have to do.
I have a date table in which there is the year, the week number and the start of the week date(date of each monday). How can I proceed now? I tried to sort the week number column for the star of week date but it does not work...

[Anonymous]

@amitchandak 

I ended up using your strategy to determine the Year based on the Week Start Date. Then I added this Year column to my Matrix and got the desired results. If needed, I can always put a textbox over the Year portion of the Matrix to hide that data element from being seen by the users. Thanks!

[Anonymous]

@amitchandak 

I haven't tried your recommendation yet but it sounds like it will get me the desired results but is there a way to leave out the Year from the Matrix Visual but still get the desired Week Number ordering?