Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Newwst to oldest date

I have a mtrix that looks as folllows:

 

 

 

The Dates are weekly dates (Every Sunday) and I h ave the history since March. However, I show only the most recent 4 weeks by using: 

 

Rank = RANKX(REQUISITION, REQUISITION[RHL_LD_DT], ,DESC, Dense)

 

So Dates already has some index assigned to it. However, I want to be able to show the matrix dates as newest date to oldest date. However, I am not able to sort by the column date in the matrix. Also, when I try to sort this date field with the Rank field under Sort column, it says:

 

Is there any way I can show newest to oldest date order for the above matrix? Thank you!

Need Help
Show and Tell
  • jdbuchanan71's avatar
    jdbuchanan71
    7 years ago

    Hello Anonymous 

    Sorry, I was not thinking it all the way through.  We will need to do a calculated table and apply the sorting there then join it back to your data.

     

    The Sorted Dates table is like this

    Sorted Dates = 
ADDCOLUMNS(
DISTINCT( 
    Sheet1[RHL Load Date]),
    "DateSort", - DATEDIFF ( TODAY (), Sheet1[RHL Load Date] , DAY )
)

    And then you apply the sorting of the date in that new table based on the DateSort column.

    Then join it to your main table but use the [RHL Load Date]

    I have attached my sample .pbix for you to look at.

     

8 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    7 years ago

    Hello Anonymous 

    You could make a calculated table that sorts the dates in the order you want.

    Sorted Dates =
SUMMARIZECOLUMNS (
    REQUISITION[RHL_LD_DT],
    "DateSort", - DATEDIFF ( TODAY (), SELECTEDVALUE ( REQUISITION[RHL_LD_DT] ), DAY )
)

    In this table you would set the sorting of the [RHL_LD_DT] column to use the [DateSort] column.  Join this into your model and use the dates from this table in your matrix.

     

    Or, if you want you could just add the sorting column into your data table.  That would be a new column something like this.

    DateSort = - DATEDIFF ( TODAY(), REQUISITION[RHL_LD_DT], DAY )

    Then set the sorting on the [RHL_LD_DT] field to use the DateSort column.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      hi jdbuchanan71  It gives me the same error as it happens when i try to sort it by rank. It says i cannot sort it as it is already sorted by the column directly or indirectly

      • jdbuchanan71's avatar
        jdbuchanan71
        Icon for Super User rankSuper User
        7 years ago

        Anonymous 

        You set the sorting in the model, on the table, not in the visual.

        The sorting you pick here is applied when the field is used in the visual.