Forum Discussion

LostinDaxland's avatar
LostinDaxland
Regular Visitor
2 years ago
Solved

Probably simple date related lookup

Hi - I'm looking to create a lookup (or rule) wherein a lookup will return the value in a cell that corresponds to a date between both the source and the lookup. I'm not explaining this well, so let's use an example. 

 

Alex started a position on 1/1/2024 in Department A. He then started another department, B, on 3/1/2024. Anything that he did between 1/1/2024 and 2/28/2024 should be credited to Department A. Anything after 3/1/2024 should be credited to Department B. How do I make this happen? The grey table example on the left will be the lookup table and the white table on the right would be the live dataset.

 

 

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    2 years ago

    LostinDaxland 

    you can try this

     

    Column =
    VAR _date=maxx(FILTER('Table','Table (2)'[Name]='Table'[Name]&&'Table'[StartDate]<='Table (2)'[Date]),'Table'[StartDate])
    return maxx(FILTER('Table','Table'[Name]='Table (2)'[Name]&&'Table'[StartDate]=_date),'Table'[Department])
     
     
    pls see the attachment below

1 Reply

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    2 years ago

    LostinDaxland 

    you can try this

     

    Column =
    VAR _date=maxx(FILTER('Table','Table (2)'[Name]='Table'[Name]&&'Table'[StartDate]<='Table (2)'[Date]),'Table'[StartDate])
    return maxx(FILTER('Table','Table'[Name]='Table (2)'[Name]&&'Table'[StartDate]=_date),'Table'[Department])
     
     
    pls see the attachment below