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'...
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.

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.