Dynamic Date Filter based on Events

[Anonymous]

Dear Community,

I am beginner in DAX and kindly ask you for your help with following issue:

I have two tables whereas one date table should act as a dynamic filter for the other table.

The date table looks like this:

Date	Event
01.01.2019
02.01.2019	Board Meeting Q1
03.01.2019
04.01.2019
05.01.2019
06.01.2019
07.01.2019
08.01.2019
09.01.2019
10.01.2019
11.01.2019
12.01.2019	Board Meeting Q2
13.01.2019
14.01.2019
15.01.2019

With applied filter the other table should show only entries "after latest demand board until today". Any idea how I could set up the filter/measure?

Thanks a lot!

Greetings

Juni15

[MFelix]

Hi @Anonymous ,

Although your information is reduced, there is several options to have the result you want:

• Create a slicer for the date column and format as after or a before (this needs to have both tables related)
• Create a measure similar to this (disconnected tables needed):

Filter = IF(MAX(Events[Date]) >= MAX('Calendar'[Date]) ; 1 ; 0)

• Use above measure as filter on the visuals you need making the filter as 1.

Regards,

MFelix

[Anonymous]

Thanks a lot @MFelix 

With your proposals I would get a list with an end date as filter (or i got it wrong :D). I am more looking for a dynamically changing date range from "last meeting until today" to filter the other table "resolution date".

The date table with Events looks like this:

Date	Event
15.07.2019
16.07.2019	Board Meeting Q1
17.07.2019
18.07.2019
19.07.2019
20.07.2019
21.07.2019
22.07.2019
23.07.2019
24.07.2019
25.07.2019
07.11.2019	Board Meeting Q2
08.11.2019
09.11.2019
10.11.2019

It should filter this table (results are red marked)

Resolution Date	Demand
15.07.2019	ABC123
16.07.2019	ABC124
14.07.2019	ABC125
13.01.2019	ABC126
16.07.2019	ABC127
17.07.2019	ABC128
18.08.2019	ABC129
09.01.2019	ABC130
10.01.2019	ABC131
11.01.2019	ABC132
12.01.2019	ABC133
13.01.2019	ABC134
14.01.2019	ABC135
15.01.2019	ABC136

Greetings, 

Juni15

[MFelix · Accepted solution]

Hi @Anonymous ,

Use the following code for you filter mode:

Filter =
IF (
    MAX ( Events[Resolution Date] )
        >= CALCULATE (
            MAX ( 'Calendar'[Date] );
            FILTER (
                ALL ( 'Calendar'[Event]; 'Calendar'[Date] );
                'Calendar'[Event] <> BLANK ()
                    && 'Calendar'[Date] <= TODAY ()
            )
        );
    1;
    0
)

Don't know if your tables are connected or not but works in the same way if you use the resolution date on your visual.

Check PBIX file attach. On the file I have place two table the one that as the filter column is just for you to check that the 1 and 0 are on the correct data.

Regards,

MFelix