Weekly view in chronological order

[eszter_bn]

Hi,

I need to build a report where I show rolling 3m performance by week - I created a column that shows start of the week, and a week and year column (format YYYYWW) to sort my weeks by, but when I try to sort my start of week column I get an error message that I need to sort by another column as there's more than one value.....

Any idea how I can fix this? It's been driving me crazy all day....

[v-xinc-msft]

Hi @eszter_bn ,

Did the reply speedramps offered help you solve the problem, if it helps, you can consider to accept it as a solution so that more user can refer to, or if you have other problems, you can offer some information so that can provide more suggestion for you.

Thanks for your understanding. Your time and cooperation are much valued by us. We are looking forward to hearing from you to assist further.

Best regards,

Lucy Chen

[speedramps]

@eszter_bn 

Have you tried my example PBIX (see previous message)

You example does not make sense, because there over 20 days with 25/11/2024 but should only be 7 days per week.

What is the error message?

You will get an error when using "Sort By Column" if there are duplicates. That is normal.

There is a dirty workaround. Filter the query to just the first record. Apply the "Sort By Column".

Then remove the filter. It bypasses the duplicate validation 

Please accept my first solution. It works. Thank you

[eszter_bn]

I have thousands of transactonal records, each with an associated transaction date - the start of week is defined depending on this transaction date, hence the duplication. What I don't really understand is, I did the same with last years data only where I only used week number (instead of year and week) and it worked perfectly fine. I also did the same with month and year as you can see below (same volume of records) and I had no issue sorting the 'month year' column by the 'month year sort' column, it's working exactly as I want it to. 

[speedramps · Accepted solution]

Download my example PBIX solution from Onedrive
Click here 

We are unpaid volunteers and you have got free expert help which took a lot of effort.
This solution works and does exactly what you asked.
So please quickly click the [accept as solution] and the thumbs up button to leave kudos.

Please try refrain from expanding this question.
If you need to change or extend your request then please raise a new ticket.
One question per ticket please !
You will get a quicker response and each volunteer solver will get the kudos they deserve.

If you quote @speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.
However I will respectfully decline to help members who initially ask misleading or incomplete questions and then wont accept the correct soltion. Hope you apprecaite why.

Please now click the [accept as solution] and the thumbs up button. Thank you

[AlexisOlson]

I created a column that shows start of the week

What do the values in this column look like?

[eszter_bn]

So it's the 'Week' colum that has the start of each week (this I created in Power Query using Date.StartofWeek using a transactional date column). This is in a text format but I tried to convert it to date, I get the same error message. I tried to use the Week-Year Sort column to sort the week column by, this is whole numbers.