Dynamic Ranking based on date and key

Question

This is my sample input data table:

entry_id entry_date company_id objective_title objective_status key                   objective_rank
1        2024-03-01 company_1  objective_1     Completed        company_1_objective_1 1
2        2024-12-30 company_1  objective_1     In Progress      company_1_objective_1 2
3        2024-01-02 company_2  objective_2     Not Started      company_2_objective_2 1
4        2023-11-10 company_3  objective_3     Completed        company_3_objective_3 1

As you can see, I've already created there a a calculated column for ranking with the following DAX formula:

objective_rank = 
    RANKX(
        FILTER(
            table_1,
            table_1[key] = EARLIER(table_1[key])
        ),
        table_1[entry_date],
        ,
        DESC
    )

And this gave me the correct results when trying to see Count of Key per latest objective status for all dates (rank = 1):

objective_status count_key
Not Started      1
In Progress      0
Completed        2

Now I'd like to have a slicer that would allow the user to select specific date range (based on entry_date).

So, if the entry_date would be e.g. < 2023-12-31 then I'd like to see the following output:

objective_status count_key
Not Started      0
In Progress      1
Completed        1

because although objective_1 "In Progress" it's not the latest overall it is the latest for a given date range.

How to achieve that?

I have tried to use replies to similar issues on Microsoft Fabric Community but they didn't seem to work in my case.

Answer 1

I think your sample data is incorrect - I have changed the date on the second row to make your question make sense. To get a working solution, follow these steps:

table_1:

Create a date table and relationship:

Date = CALENDARAUTO() 

Create two measures:

objective_rank = 
VAR x = SELECTEDVALUE(table_1[key])
RETURN
VAR r = 
    RANKX( FILTER( ALLSELECTED(table_1), table_1[key] = x), CALCULATE( MIN( table_1[entry_date])), , DESC)

RETURN   IF(NOT ISEMPTY(table_1)  , r)


My Count = CALCULATE( COUNTROWS(table_1), FILTER(ALLSELECTED(table_1),  [objective_rank] = 1), table_1[objective_status] = SELECTEDVALUE(table_1[objective_status]))+0

Add a slicer using your date table:

Add a count table:

Add a master table:

Results with nothing selected:

Results with 31st December 2023: