Forum Discussion
Sort Latest Week Value
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.
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 _filterIf 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.
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.
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
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.
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:
