Forum Discussion

SMaz's avatar
SMaz
Frequent Visitor
1 year ago
Solved

Filter a table with dynamic dates

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 !

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.