Sort Latest Week Value

[vividarinda]

Hi All,

How to sort the latest week values from the matrix below? 

[Anonymous · Accepted solution]

Hi @vividarinda ,

(1)We can create a date table.

Date = ADDCOLUMNS(CALENDAR(DATE(2024,1,1),DATE(2024,12,31)),"week",WEEKNUM([Date]))

(2)We can create a column and a measure.

ExtractedNumber = 
VAR FirstSpace = SEARCH(" ", 'Table'[Week], 1)
VAR SecondSpace = SEARCH(" ", 'Table'[Week], FirstSpace + 1)
RETURN MID('Table'[Week], FirstSpace + 1, SecondSpace - FirstSpace - 1)

Measure = var _max=MAXX(ALLSELECTED('Date'),[week])
var _filter=MAXX(FILTER(ALLSELECTED('Table'),[Description] in VALUES('Table'[Description])&&[ExtractedNumber]=_max),[value])
return _filter

(3)Creating Model Relationship.

(4)Sort - Turn off text wrap - Manually adjust column width to hide.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Anonymous]

Hi @vividarinda ,

First of all thanks to @Joe_Barry  for the quick reply. I have some other thoughts to add:

(1)This is my test data.

(2) We can create columns.

ExtractedNumber = 
VAR FirstSpace = SEARCH(" ", 'Table'[Week], 1)
VAR SecondSpace = SEARCH(" ", 'Table'[Week], FirstSpace + 1)
RETURN MID('Table'[Week], FirstSpace + 1, SecondSpace - FirstSpace - 1)

RANK = 
var _max= MAX('Table'[ExtractedNumber])
var _rank=RANKX(FILTER('Table',[ExtractedNumber]=EARLIER('Table'[ExtractedNumber])),[value],,DESC)
RETURN IF([ExtractedNumber]=_max,_rank,BLANK())

(3)Place the rank columns on the matrix visual object, set sorting by ascending order of the rank columns, and then manually adjust the column widths to hide the rank columns while turning off [text wrap] for the column headers.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[vividarinda]

Hi @Anonymous ,

Thank you for the quick response. However, my current situation involves using a date filter, where the data retrieved is the latest 4 weeks based on the filter date and sorting the values in ascending order based on the selected week. As an example, if I filter from April 27th to May 26th, the visualization will only display data from the last 2 weeks out of the 4 weeks included in the filter

[Anonymous · Accepted solution]

Hi @vividarinda ,

(1)We can create a date table.

Date = ADDCOLUMNS(CALENDAR(DATE(2024,1,1),DATE(2024,12,31)),"week",WEEKNUM([Date]))

(2)We can create a column and a measure.

ExtractedNumber = 
VAR FirstSpace = SEARCH(" ", 'Table'[Week], 1)
VAR SecondSpace = SEARCH(" ", 'Table'[Week], FirstSpace + 1)
RETURN MID('Table'[Week], FirstSpace + 1, SecondSpace - FirstSpace - 1)

Measure = var _max=MAXX(ALLSELECTED('Date'),[week])
var _filter=MAXX(FILTER(ALLSELECTED('Table'),[Description] in VALUES('Table'[Description])&&[ExtractedNumber]=_max),[value])
return _filter

(3)Creating Model Relationship.

(4)Sort - Turn off text wrap - Manually adjust column width to hide.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[vividarinda]

Hi @Anonymous ,

Thank you for your quick response. Your suggestion is amazing—I’ve already applied the logic for weekly tasks. However, I encountered an issue when trying to apply the same logic for monthly tasks. See the image below:

[Anonymous · Accepted solution]

Hi @vividarinda ,

We can create a date table.

Date = ADDCOLUMNS(CALENDAR(DATE(2024,1,1),DATE(2024,12,31)),"week",WEEKNUM([Date]),"month",MONTH([Date]))

This is my test data. We can create columns.

_month = 
LEFT([month], FIND(" ", [month]) - 1)

_month_num = MONTH( CONVERT([_month]&" "&1,DATETIME))

We can create a measure.

Measure 2 = 
var _max=MAXX(ALLSELECTED('Date'),[month])
var _filter=MAXX(FILTER(ALLSELECTED('Table (2)'),[Description] in VALUES('Table (2)'[Description])&&[_month_num]=_max),[value])
return _filter

If you still have problems following up, please create a new case, we suggest a case to solve only one problem, because it can get good help and give a better reference for other users!

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[vividarinda]

Hi @Anonymous ,

Thanks, I really appreciate your support. Next, I will address a separate issue in a different context.

[vividarinda]

Hi @Joe_Barry ,

Thanks for your reply. My expectation result like this. Sort the values in descending order starting from last week : 134, 127, 127, etc

[Joe_Barry]

Hi @vividarinda 

• Do you have a column in your table called week number? If you don't you can create one in Power Query. Highlight the Week column
• Go to the ribbion and choose Add Column > Extract > Extract after delimiter.
• In the field hit your space bar and presss ok.
• A new column withe a number will be created convert to Whole number if needed.
• Load the table.
• Go to the Table View and highlight the old Week column
• Go to the ribbion > Column tools > Sort by column and choose the new column
• The visual can now be sorted correctly. You can choose additional sort options if you hover over the top right of the visual

Joe