Visual filter to show data when date X is less than date Y

[Anonymous]

I have two tables with data.

Table 1 has information about employees working hours.

Table 2 contains information about when an employee is leaving the company. If an employee is not leaving, the cell is blank. 

Since Brian's termination date is 18.01.2019, the hours registered in Table 1 for 25.01.2019 is inaccurate and should not be displayed in the visual. Using Visual Filters, how can I filter out that last row in Table 1?

I created a measure but it does not work as intended: 

limit_data = IF(FIRSTNONBLANK(Table2[Termination Date], 1) > FIRSTNONBLANK(Table1[PERIOD], 1), 0, 1)

I then added the measure to "Filters on this visual":

My visual is using measures and slicers that should effect the visual, so I cannot use calculated table. I hope there's a way using Visual Filter?

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

You could use LOOKUPVALUE() function to make a new measure.

limit_data =
VAR a =
    LOOKUPVALUE (
        'Table2'[Termination Date],
        'Table2'[Name], SELECTEDVALUE ( 'Table1'[Name] )
    )
RETURN
    IF ( a >= SELECTEDVALUE ( 'Table1'[Period] ), 1, BLANK () )

Then you can add this measure to visual filter and set "show items when the value is not blank".

[Anonymous]

I marked your reply as the solution.

I attempted to simplify it by 

limit_data = IF(SELECTEDVALUE(Table2[Termination Date]) > SELECTEDVALUE(Table1[PERIOD]), 1, BLANK())

It seems to do the same thing, but I don't know if either one is more effective than the othe. 

Thanks 🙂

[Anonymous]

@Anonymous 

Alternatively, you can achieve it by the calculated column. Before that ensure both the tables are in one-to-many relationship

Column = IF(Tabl1[Period]<=RELATED(Tabl2[T Date]),Tabl1[Hours])

[Anonymous]

I don't think a calculated table will solve it. I also cannot have relationships between these two tables. There are other tables in effect.

[Anonymous]

@Anonymous 

I'm not sure that we can make it without relationship between the tables. Let's see if we get some replies from experts.

Nandri