Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Calculated Column - Receive Value Based on Nearest Date

Hi,    I'm trying to create a calculated column from a second table based on a date value. The goal is to display the actual level for each account before a campaign has been send.    Levels ...
Jos_Woolley's avatar
Jos_Woolley
Solution Sage
4 years ago

Hi,

I assume your two tables do not have a Many-to-Many relationship on the AccountID column?

Level on Campaign Date =
VAR AccountID = Campaign[AccountID]
VAR CampaignDate = Campaign[CampaignDate]
RETURN
    CALCULATE(
        MAX( 'Levels'[NewFunnelLevel] ),
        FILTER(
            'Levels',
            'Levels'[AccountID] = AccountID
                && 'Levels'[ChangeDate] <= CampaignDate
        )
    )

Regards

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    They do have an inactive many-to-many relationship but also connected via Account table. Sadly I didn't got %100 correct values on all rows.