Returning the value of a 5th item from a table in a calculated measure

[Anonymous]

Hi guys, 

I'm faced with a problem I cannot seem to be able to crack. I would like to create a measure that would return the 5th latest date from this table:

BusinessDate20190501
20190430
20190429
20190426
20190425
20190424
20190423
20190422
20190419
20190418

Meaning that I would like to create a calculated measure that returns 20190425 .

So first I wanted to rank the values in the table. For that I used following DAX statement:

Rank BD:=
RANKX(
    ALL(BD[businessdateid]),
    SUM(BD[businessdateid]),
    ,
    0,
    Skip
)

This measure works fine when you place the businessdateid on the dimension. Then the evaluation context is ok and for each businessdateid it shows the correct rank. The problem is I would like to use this [RANK BD] without using the businessdateid as a dimension in the cube to return the value. So i have written the following DAX:

myval:=
CALCULATE(
    MAX(BD[businessdateid]),
    FILTER(ALL('BD'[businessdateid]), [Rank BD]=5)
)

This as expected doesn't show anything if you dont explicitly drag the businessdateid into an axis, as from what i can understand it takes ALL businessdateid values together and sums their values into one, always resulting in RANK = 1. 

Is it possible somehow to enforce the evaluation context in the formula so that it "loops" over businessdateid and picks the 5th?

Many thanks in advance for your help on this one!

[technolog · Accepted solution]

Hello,

To achieve the desired outcome of returning the 5th latest date from your table in a calculated measure without explicitly using businessdateid as a dimension, you can modify your DAX approach. The key is to construct a measure that can correctly identify the 5th latest date based on your existing ranking.

Given the scenario you've described, your current ranking measure seems fine, but the challenge lies in applying this rank to retrieve the specific date. The following DAX formula should help you in achieving this:

FifthLatestDate :=
VAR RankedDates =
ADDCOLUMNS(
ALL('BD'),
"Rank", RANKX(ALL('BD'), [businessdateid], , DESC, Dense)
)
VAR FifthDate =
CALCULATE(
MAX('BD'[businessdateid]),
FILTER(RankedDates, [Rank] = 5)
)
RETURN
FifthDate