Filter a table with dynamic dates

[SMaz]

Hi everyone,

I have two tables : 

1. Classic Calendar table (date)

2. Project table (Project_code, Signature_date, Admin_launch_date, ...)

I want to create a report where I can :

1. Know how many projects have passed each milestone (signature, admininistrative launch, ...) => card with the measure

Nb_projects_signed_on_period = CALCULATE(COUNTA(PROJECTS[Project_code]), FILTER(PROJECTS, PROJECTS[SIGNATURE_DATE] >= MIN('CALENDAR'[DATE]) && PROJECTS[SIGNATURE_DATE] <= MAX('CALENDAR'[DATE])))

2. Visualize the projects that have passed each milestone => tables that needs to be filtered

My issue is that I can't find how to filter my tables to get the correct projects that passed each milestone. 

Note : There are no relationships between the Calendar table and the Project table.

Thanks for your help !

[SMaz]

Thanks a lot !

[v-jiewu-msft · Accepted solution]

Hi @SMaz ,

Based on the description, you can change the calendar date range in the slicer visual to filter the table data.

Try using the following DAX formula to create a new measure.

Measure = 
var _date = SELECTEDVALUE('Project Table'[Signature_date])
RETURN
IF(_date >= MIN('Table'[Date]) && _date <= MAX('Table'[Date]), 1, 0)

Then, set the show item is 1 in Filters pane.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.