Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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.
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.
@wifc5013 , Try a new measure like
Countx(allselected(Table), Table[Date]>= min(Table[DAte]), Table[DAte])
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.
User | Count |
---|---|
57 | |
21 | |
19 | |
17 | |
16 |
User | Count |
---|---|
85 | |
80 | |
52 | |
37 | |
22 |