Forum Discussion

StefRenneboog91's avatar
StefRenneboog91
Regular Visitor
5 years ago
Solved

BEGINNER LEVEL - FILTER ON DATE

Hey all,

 

Thanks a lot already for helping me out in this topic.

I'm having 2 tables (PA0000 & PA0001)

 

With let's say 2 records in PA0000 (PERNR = 1 & PERNR = 2) & 2 records in PA0001 with also PERNR 1 & 2 but with different start & end date.

 

Now I want to set up a measure/calculated column/column where I get a 1 for PERNR 1 and a 0 for PERNR 2 because BEGDA of PERNR1 <= TODAY & ENDDA of PERNR 1 >= TODAY and PERNR of PA0000 (1 in this case) is found in PA0001.

In "excel terms" I want to do a VLOOKUP of the PERNR in table PA0001 but with the extra filter of the date.

 

Can someone help me out on this one please?
Thanks a lot!
KR,
Stef
Need Help
  • DataInsights's avatar
    DataInsights
    5 years ago

    StefRenneboog91,

     

    Try this measure:

     

    Lookup Result =
VAR vToday =
    TODAY ()
VAR vLookupTable =
    FILTER ( PA0001, vToday >= PA0001[BEGDA] && vToday <= PA0001[ENDDA] )
VAR vResult =
    IF ( COUNTROWS ( vLookupTable ) = 0, 0, 1 )
RETURN
    vResult

     

    Create table visual with PA0000[PERNR]:

     

     

1 Reply

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    5 years ago

    StefRenneboog91,

     

    Try this measure:

     

    Lookup Result =
VAR vToday =
    TODAY ()
VAR vLookupTable =
    FILTER ( PA0001, vToday >= PA0001[BEGDA] && vToday <= PA0001[ENDDA] )
VAR vResult =
    IF ( COUNTROWS ( vLookupTable ) = 0, 0, 1 )
RETURN
    vResult

     

    Create table visual with PA0000[PERNR]: