How to sort based on text column which will change every week

[ambi95]

Hi, I am stuck here.

Attached is the matrix i have on PBI. 

The column headers are static data entered as a text type, showcasing a week's date range. This data will change every week.

How can i sort it in such a way that (in this example) 30th March - 5th Apr is first, 6th - 12th Apr is second, and then > 13th Apr is third.

I'm really confused, if anyone could provide an alternative to sorting directly also, that would work.

Just need anything that works

@amitchandak 

[Ahmedx · Accepted solution]

see my video
https://1drv.ms/v/s!AiUZ0Ws7G26RhxEUJVRvC1njL21i?e=MSsHdp
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
https://1drv.ms/u/s!AiUZ0Ws7G26RhxCRRw-4sg9a49rB?e=F4gmfQ

List.Max(
List.Transform(
Text.Split([#"ColB - Copy"],"-"),(x)=> 
Number.From(try Date.FromText(Text.Trim(x), [Format = "dd MMM yy", Culture = "en-EN"])otherwise 
Date.FromText(Text.Trim(x), [Format = "dd MMM yy", Culture = "en-EN"]
))))

[ambi95]

this worked! thanks a ton

[ambi95]

@Ahmedx thanks a lot for the support, i will try this solution and keep you updated on the progress.

[Ahmedx]

Share sample pbix file to help you.

[ambi95]

Link to Sample Files 

@Ahmedx  please check above link

[ambi95]

@Ahmedx can you please elaborate? i am a fairly new PBI user

[Ahmedx]

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column?tabs=powerbi-deskto...

https://www.youtube.com/watch?v=uZLmWz60_do&ab_channel=MyOnlineTrainingHub

[ambi95]

@Ahmedx thanks so much for the links, but my main problem is that the data will change every week , as you can see it is weekly data. How can i implement dynamic custom sorting? 

[Ahmedx]

you need to add an index column next to
Column header and it must be dynamic.
write how you enter the name of these columns as text?