Dynamic date filter based in other filter

[Anonymous]

Hello,

I have a table with project name and update column (date type). It is supposed that all records have similar week update, however, there are some records with an older week update.

I have a page with two filters which shows the report for each project. So first filter is project name and second filter is week.

I want a dynamic filter for week: when I select the project name, I want that the week filter automatically shows (by default) the last week update for this project.

Project Name	Update	Week
Project A	05-06-2020	24
Project A	12-06-2020	25
Project A	19-06-2020	26
Project B	05-06-2020	24
Project B	15-06-2020	25
Project B	19-06-2020	26
Project C	12-06-2020	25
Project C	19-06-2020	26
Project D	05-06-2020	24
Project D	12-06-2020	25

In this example, when I select Project A, week filter should be by defaut week 26 (and optional weeks 24 ans 25). And if I select Project D, week filter should be by defauld week 25 (and optional week 24).

Thanks!

[v-shex-msft · Accepted solution]

Hi @Anonymous,

Maybe you can create a measure to extract the last week based on current project group and compare it with the current date to return tag, then you can see this on your filter to filter records.

Measure filter =
VAR currDate =
    MAX ( Table[Week] )
VAR _lastDate =
    CALCULATE (
        MAX ( Table[Week] ),
        ALLSELECTED ( Table ),
        VALUES ( Table[Project Name] )
    )
RETURN
    IF ( curDate = _lastDate, "Y", "N" )

Regards,

Xiaoxin Sheng

[amitchandak]

@Anonymous , I doubt that is possible

[Anonymous]

Do you know a way to clean the week filter each time when when I select a project in the project filter?
Therefore, it will force that the user select a week, preventing mistakes.