Filter data with from and to dates using week commencing

[JordB]

Hi,

I have one table with multiple fields which i want to display, two of the fields are from and to dates.

I wish to filter the data displayed using a week commencing date.

I created a separate table with two fields, date and week commencing date but can't figure out how to filter the other table using it.

E.g User selects wc 21/05/2017

I want to display all rows which have a start date < (21/05/2017 + 6) and a finish date > 21/05/2017

Hope that makes sense

[v-ljerr-msft · Accepted solution]

Hi @JordB,

If I understand you correctly, you should be able to follow steps below to get your expected result.

I assume your first table with start/finish date called "Table1", and the second table with week commencing date called "Table2".

1. Remove the relationship between "Table1" and "Table2" if there is.

2. Use the formula below to create a measure to indicate if the rows have a start date < ( the selected wc + 6) and a finish date > selected sc.

IsSelected =
VAR currentSelectWC =
    MAX ( 'Table2'[wc] )
RETURN
    CALCULATE (
        COUNTROWS ( 'Table1' ),
        FILTER (
            'Table1',
            'Table1'[start date]
                < currentSelectWC + 6
                && 'Table1'[finish date] > currentSelectWC
        )
    )

3. Then you should be able to use the measure [IsSelect] as a visual level filter on the visualization you're showing your fields, and apply "is greater than 0" to get only selected result. 

Regards

[JordB]

Worked a treat, thanks.