Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Look Up Value with Fallback result

Hi All, 

 

I am trying to create a measure that looks up a value in a lookup table based on a combination of date and region. If that combination doesn't exist in the lookup table, then it should use the value for the previous month for that region. 

 

Here is my Measure, but it is returning blanks for combinations that don't exist in the lookup table. 

 

LookupValueWithFallback =
VAR CurrentDate = MAX(CombinationTable[Date])
VAR CurrentRegion = MAX(CombinationTable[Region])

VAR LookupResult =
    CALCULATE(
        MAX('Lookup Table'[Value]),
        FILTER(
            'Lookup Table',
            'Lookup Table'[Date] = CurrentDate &&
            'Lookup Table'[Region] = CurrentRegion
        )
    )
VAR FallbackDate = EOMONTH(CurrentDate, -1)
VAR FallbackResult =
    CALCULATE(
        MAX('Lookup Table'[Value]),
        FILTER(
            'Lookup Table',
            'Lookup Table'[Date]= FallbackDate &&
            'Lookup Table'[Region] = CurrentRegion
        )
    )

RETURN
    IF(
        NOT ISBLANK(LookupResult),
        LookupResult,
        FallbackResult
    )
 
Any idea where I am going wrong? 
Need Help
  • parry2k's avatar
    parry2k
    2 years ago

    Anonymous here is the fix and the output:

     

    LookupValueWithFallback = 
VAR CurrentDate = MAX('Combination Table'[Date]) // Replace with your actual date column
VAR CurrentRegion = MAX('Combination Table'[Region]) // Replace with your actual region column

VAR LookupResult =
    CALCULATE(
        MAX('Lookup Table'[Rolling Headcount]),
        FILTER(
            'Lookup Table',
            'Lookup Table'[Date] = CurrentDate &&
            'Lookup Table'[Region] = CurrentRegion
        )
    )
VAR FallbackDate = EOMONTH(CurrentDate, -2) + 1
VAR FallbackResult =
    CALCULATE(
        MAX('Lookup Table'[Rolling Headcount]),
        FILTER(
            'Lookup Table',
            'Lookup Table'[Date]= FallbackDate &&
            'Lookup Table'[Region]= CurrentRegion
        )
    )

RETURN
    IF(
        NOT ISBLANK(LookupResult),
        LookupResult,
        FallbackResult
    )

     

     

6 Replies

  • parry2k's avatar
    parry2k
    Super User
    2 years ago

    Anonymous here is the fix and the output:

     

    LookupValueWithFallback = 
VAR CurrentDate = MAX('Combination Table'[Date]) // Replace with your actual date column
VAR CurrentRegion = MAX('Combination Table'[Region]) // Replace with your actual region column

VAR LookupResult =
    CALCULATE(
        MAX('Lookup Table'[Rolling Headcount]),
        FILTER(
            'Lookup Table',
            'Lookup Table'[Date] = CurrentDate &&
            'Lookup Table'[Region] = CurrentRegion
        )
    )
VAR FallbackDate = EOMONTH(CurrentDate, -2) + 1
VAR FallbackResult =
    CALCULATE(
        MAX('Lookup Table'[Rolling Headcount]),
        FILTER(
            'Lookup Table',
            'Lookup Table'[Date]= FallbackDate &&
            'Lookup Table'[Region]= CurrentRegion
        )
    )

RETURN
    IF(
        NOT ISBLANK(LookupResult),
        LookupResult,
        FallbackResult
    )

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thank you. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      parry2k ,

       

      Look up value based on a combination of date and region. If that value doesn't exist in the lookup table, then use last month's value.