How to get the previous row Value In Dax like LAG in TSQL

[sekela]

I want to get the previous value partitioned by country and order by year like the screenshot below highlighted in red.

I tried the following and the result is not as expected

PreviousValue =

CALCULATE (

MAX ( Sheet1[Total Sales] ),

FILTER (

Sheet1,

EARLIER ( Sheet1[Year] ) > Sheet1[Year]

&& EARLIER ( Sheet1[RowNumber] ) > Sheet1[RowNumber]

&& EARLIER ( Sheet1[Country] ) = Sheet1[Country]

)

)

[tamerj1 · Accepted solution]

Hi @sekela 

This calculate column code should work

PreviousValue =
VAR CurrentCountry = Sheet1[Country]
VAR CurrentYear = Sheet1[Year]
VAR FilteredTable =
    FILTER (
        Sheet1,
        Sheet1[Country] = CurrentCountry
            && Sheet1[Year] = CurrentYear - 1
    )
VAR Result =
    SUMX ( FilteredTable, Sheet1[Total Sales] )
RETURN
    Result

[ValtteriN]

Hi,

Could you add sample data in a table format and a snippet about the "not expected values"? For now, I recommend trying to reverse < directions here:

PreviousValue =

CALCULATE (

MAX ( Sheet1[Total Sales] ),

FILTER (

Sheet1,

EARLIER ( Sheet1[Year] ) < Sheet1[Year]

&& EARLIER ( Sheet1[RowNumber] ) < Sheet1[RowNumber]

&& EARLIER ( Sheet1[Country] ) = Sheet1[Country]

)

[sekela]

Year	Country	Quantity	Unit price	Total Sales	RowNumber
2017	USA	5	10	50	1
2018	USA	21	5	105	2
2019	USA	4	11	44	3
2020	USA	8	3	24	4
2021	USA	12	7	84	5
2017	Japan	2	4	8	1
2018	Japan	6	11	66	2
2019	Japan	8	2	16	3
2020	Japan	9	13	117	4
2021	Japan	3	21	63	5
2017	China	7	3	21	1
2018	China	8	1	8	2
2019	China	21	35	735	3
2020	China	1	20	20	4
2021	China	6	15	90	5