cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors