Lookup previous value based on criteria from another column

[RogerSteinberg]

Hi All,

I would like to get the value that preceedes based on the earlier date. The value needs to also match the group number.

My table looks like that:

+------------+-------+-------+----------------+
|    date    | group | value | previous value |  
+------------+-------+-------+----------------+
| 2019-02-02 |     2 |    50 |             45 |  
| 2019-02-02 |     1 |    60 |             80 |  
| 2019-01-18 |     2 |    45 |                |  
| 2019-01-18 |     1 |    80 |                | 

After looking different ressources online that ressemble my question I came up with the following query which gives me a blank column.:

RateChange = 
CALCULATE(
    VALUES(table[Rate]),
    FILTER(
        ALL(table),
        table[date] = EARLIER(table[date])-1  && table[group] = EARLIER(table[group])
    )
)

 I need help in getting the query to work as calculated column and measure.
Thank you !

[v-lili6-msft · Accepted solution]

hi, @RogerSteinberg 

Is this the case that there are two or more value for one group on the one date,

if so the previous value should be sum/ max/ min value?

You could also try this way:

Step1:

Add a group rank column for each group by date

Group rank = RANKX(FILTER('table','table'[group]=EARLIER('table'[group])),'table'[date],,ASC,Dense)

Step2:

Use this formula to get the result

Result = 
CALCULATE (
    SUM ( 'table'[value] ),
    FILTER (
        'table',
        'table'[group] = EARLIER ( 'table'[group] )
            && 'table'[Group rank]
                = EARLIER ( 'table'[Group rank] ) - 1
    )
)

Result:

Best Regards,

Lin

[Zubair_Muhammad]

@RogerSteinberg 

Try this pattern

=
MINX (
    TOPN (
        1,
        FILTER ( 'Table', [group] = EARLIER ( [group] ) && [date] < EARLIER ( [date] ) ),
        [date], DESC
    ),
    [Rate]
)

[BJFH]

Hi Muhammad, thank you for this suggestion - it worked brilliantly for what I needed it for - finding the most recent value by concatenating several criteria into a single index column - i.e. the "group" in this example.

This formula found the most recent value, I also need to find the value closest to the date 1 week prior.  Any ideas / suggestions on how I'd do that?

Thanks

Ben