Nth Position of Date in DAX

DAX DAX Calculations
1

Hi.
I have a requirement to create a report that produces First Available Interview Date, Median Point of Date between the first interview date and the 20th available interview date such that I would a report like:

image
(The image does not reflect the true values in the data but simply sample of what I want to archive.

Free Interview Date column in the table shows what date is free for interview booking against the specific interview room. Users want to see the first date of available interview appointment, the median Date between the first date available and the 20th available date and thirdly, the 20th Available free date for interview against any interview room.

My first column measure is:

First Available Interview Date =

CALCULATE (
FIRSTDATE ( ‘Interview Room’[Date] ),
‘Interview Room’[Free Interview Date] = 1
)

I get an error while trying to write the second measure. My code:

Midian Date between First and 20th =

CALCULATE (
MEDIANX ( ‘Interview Room’, ‘Interview Room’[Date] ), ‘Interview Room’[Free Interview Date] = 1 )

Please, I need help with the other two measures - Median Point between the first date available and the 20th available date and the 20th Available free date for interview against any interview room.
My file is attached.
I anticipate your help on this, please.
Thank you
Interview Slots.pbix (58.8 KB)

2

Hi @upwardD

Make use of Topn function to get 20th value. I have used below measure. Attached the solution. Replace 5 with 20 in below measure. EDNA_Solution Interview Slots.pbix (59.8 KB)

TwentyDate = 
CALCULATE (MAX ( 'Interview Room'[Date] ),
    TOPN (5,
        FILTER ( 'Interview Room', 'Interview Room'[Free Interview Date] = 1 ),
        'Interview Room'[Date], ASC  ))

Midian Date between First and 20th =
[First Available Interview Date] + INT(DATEDIFF([First Available Interview Date],[TwentyDate],DAY) / 2)

Thanks
Ankit J

3 Likes
3

Thank you @ankit