Look Up Value with Fallback result

[Klal700]

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? 

[parry2k · Accepted solution]

@Klal700 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
    )

[Klal700]

Thank you. 

[parry2k]

@Klal700 what is the logic?

[Klal700]

@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.  

[Klal700]

Hi, 

@parry2k 

https://www.dropbox.com/scl/fi/ja0sx2wcvfsohnvsi0f9j/Lookup-Value-PBI.pbix?rlkey=gxhqfzpj6mb4vhan4zk...

Power BI file with some data. 

Here, I'd like the value for Sep 2023 for Interiors to be 9 instead of blank and for Strategic Projects to be 24. 

[parry2k]

@Klal700 it will be easier if you share some data and explain what you are trying to achieve. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490