Forum Discussion

RogerSteinberg's avatar
RogerSteinberg
Icon for Post Patron rankPost Patron
7 years ago
Solved

Lookup previous value based on criteria from another column

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 !

2 Replies