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 !

[ElenaN · Accepted solution]

Hello,

You can try the following modification marked with blue:

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

Regards,

ElenaN

[v-chuncz-msft · Accepted solution]

@RogerSteinberg ,

You may refer to the post below.

https://community.powerbi.com/t5/Desktop/Finding-the-previos-occurrence-within-same-table-based-on-o...

[ElenaN · Accepted solution]

Hello,

You can try the following modification marked with blue:

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

Regards,

ElenaN