Forum Discussion

wifc5013's avatar
wifc5013
Frequent Visitor
4 years ago
Solved

Dynamic Date RANKX() by Date Selected on Slicer

I am looking for a a DAX calculated column or measure that will rank dates dynamically based on a slicer date selection.

 

The end goal is to calculate 2 tables, one with the 'latest date selected' (index = 1) and one with the 'previous date' (index = 2), based on the slicer selection. Typically I would use DATEADD() or PREVIOUSDAY() for this, but the dates are not contiguous, therefore it seemed an index was needed, but I could be wrong.

 

The measure/column needs to dynamically adjust to the filter selection. Thank you kindly for your consideration

Date
DAX
RANKX
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi wifc5013 ,

     

    Please firstly create a calendar table

    Calendar = CALENDAR(MIN('Table'[EOD_DATE]),MAX('Table'[EOD_DATE]))

    Then create rank measure:

    Rank = 
var _rank=RANKX(FILTER(ALL('Table'),[EOD_DATE]<=MAX('Calendar'[Date])),CALCULATE(MAX('Table'[EOD_DATE])),,DESC)
return 
IF(MAX('Table'[EOD_DATE]) >MAX('Calendar'[Date]),BLANK(), _rank)

     

     

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

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    wifc5013 , Try a new measure like

     

    Countx(allselected(Table), Table[Date]>= min(Table[DAte]), Table[DAte])

    • wifc5013's avatar
      wifc5013
      Frequent Visitor
      4 years ago

      Thanks for your efforts. I create a measure as follows, but the lasta parameter in the measure gives error:  

       

      Countx(allselected(Table), Table[Date]>= min(Table[DAte]), Table[DAte])

       

      "Too many arguments passed to Countx function"

       

      If i remove that, so measure become:

       

      I receive error: 

      Calculation error in measure 'USER_CASHPROMPT_PNL_RESULTS'[__RANKY]: The function COUNTX cannot work with values of type Boolean.

       

      __RANKY = Countx(allselected(Table),Table[EOD_DATE] >= max(Table[EOD_DATE]))
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi wifc5013 ,

     

    Please firstly create a calendar table

    Calendar = CALENDAR(MIN('Table'[EOD_DATE]),MAX('Table'[EOD_DATE]))

    Then create rank measure:

    Rank = 
var _rank=RANKX(FILTER(ALL('Table'),[EOD_DATE]<=MAX('Calendar'[Date])),CALCULATE(MAX('Table'[EOD_DATE])),,DESC)
return 
IF(MAX('Table'[EOD_DATE]) >MAX('Calendar'[Date]),BLANK(), _rank)

     

     

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