cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Custom Column - previous year value

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.

 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
1 ACCEPTED SOLUTION
Super User

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]
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

2 REPLIES 2
Frequent Visitor

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

Super User

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]
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.