Custom Column - previous year value

[2016Cayenne]

Hi,

I've tried many different 'earlier' solutions I've found on this forum but can't get them to work for my data.

I have a table of customer totals per product per year.  I want to create a custom column that captures the previous year's total value for each.

Thanks in advance.

customer	reportingdate	Product	total	Previous total
111111	1/1/2020	P1	123
111111	1/1/2020	P2	941
111111	1/1/2021	P1	456
111111	1/1/2022	P1	444
22222	1/1/2020	P1	3334
22222	1/1/2021	P1	3433
22222	1/1/2021	p2	1212
22222	1/1/2022	P1	5666
22222	1/1/2023	P1	6555
22222	1/1/2024	P1	6655

[2016Cayenne]

Thanks @Jihwan_Kim !  that worked and I see what I was going wrong.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

It is for creating a calculated column.

Previous year total CC =
VAR _prevyear =
    DATE ( YEAR ( data[reportingdate] ) - 1, MONTH ( data[reportingdate] ), DAY ( data[reportingdate] ) )
RETURN
    SUMX (
        FILTER (
            data,
            data[customer] = EARLIER ( data[customer] )
                && data[product] = EARLIER ( data[product] )
                && data[reportingdate] = _prevyear
        ),
        data[total]
    )