Forum Discussion

vividarinda's avatar
vividarinda
Helper II
2 years ago
Solved

Sort Latest Week Value

Hi All,

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

 

Data Protection
General Comment
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
    Anonymous
    2 years ago

    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. 

     

8 Replies

  • Joe_Barry's avatar
    Joe_Barry
    Solution Sage
    2 years ago

    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

  • vividarinda's avatar
    vividarinda
    Helper II
    2 years ago

    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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      vividarinda
      Helper II
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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.