Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Measure to find value based on effective start date

Hi,    I am trying to create a measure that looks up a value in the date with an effective start date, presuming that if there is no newer date, the value still applies.   Using the table below a...
Interesting Links
Need Help
Show and Tell
Tips and Tricks
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

    Salary on the day: =
VAR effectivedate =
    MAXX (
        FILTER ( Data, Data[Effective Date] <= MAX ( 'Calendar'[Date] ) ),
        Data[Effective Date]
    )
RETURN
    IF (
        HASONEVALUE ( Data[Employer ID] ) && HASONEVALUE ( 'Calendar'[Date] ),
        MAXX (
            FILTER ( Data, Data[Effective Date] = effectivedate ),
            Data[Daily Salary]
        )
    )
Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
4 years ago

Hi,

Please check the below picture and the attached pbix file.

 

 

Salary on the day: =
VAR effectivedate =
    MAXX (
        FILTER ( Data, Data[Effective Date] <= MAX ( 'Calendar'[Date] ) ),
        Data[Effective Date]
    )
RETURN
    IF (
        HASONEVALUE ( Data[Employer ID] ) && HASONEVALUE ( 'Calendar'[Date] ),
        MAXX (
            FILTER ( Data, Data[Effective Date] = effectivedate ),
            Data[Daily Salary]
        )
    )
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Perfect solution - thank you!