Date table relationship & dynamic Rankx based on date

[jason_m]

Hello,

Total noob here. Apologies in advance if I am unable to explain it clearly.

I wanted to show the revenues for the 10 latest trips for each of my destinations.

However, I have encountered several problems:

1. Date

-When my date slicer shows Mar 1, 2020 - Apr 30, 2020, the table still shows trip numbers outside this range.

("dates[date]" column is on a 1-to-many relationship with "DLR[RETURN date]" column).

- I tried checking the relationship using the following measure to see if this relationship is working properly, and it was:

return date = SELECTEDVALUE(DLR[RETURN date])

the "return date" measure was working as intended, based on the values in the slicer, however the "RETURN date" column seems to ignore this filter.

2. Rankx

I have made 2 measures as trial for ranking the trips based on return dates and destination:

measure 1:

trips rank = RANKX(

    FILTER(ALL(DLR),

        DLR[to]="Mombasa" && [return date] <= RELATED(Dates[Date])),

    calculate(

        SELECTEDVALUE(DLR[RETURN date]),

        ALLEXCEPT(DLR,DLR[unique trip number 2],Dates[Date]))

measure 2:

trips rank 2 = RANKX(

    FILTER(ALL(DLR),

        DLR[to]="Mombasa"),

    calculate(

        SELECTEDVALUE(DLR[RETURN date]),

        ALLEXCEPT(DLR,DLR[unique trip number 2],DLR[RETURN date]))

    ,,DESC)

Measure 1 seems to be completely wrong.

Measure 2 seems to work, but is not dynamic, and still inputs values to filtered our rows (i.e. TO <> "Mombasa"). A visual-level filter can be done here as workaround.

Before visual-level filter:

 

after visual-level filter:

if possible I would like to make these ranks dynamic (to reflect 10 latest trips based on the dates selected on the slicer)

As a final product, I would like a table with 2 columns:

1. trip number

2. revenue

which is filtered only showing a specific destination, as well as the 10 latest trip numbers based on the date in the slicer.

Could someone please help me solve these problems and make me understand the solution?

Thanks a lot.

[Anonymous]

Hi @jason_m ,

Could you provide some sample data for testing?Do remember to remove the confidential information.

Much appreciated.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!