Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
2016Cayenne
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.

 

Thanks in advance.

 

customer   reportingdate    Product   totalPrevious total
1111111/1/2020P1123 
1111111/1/2020P2941 
1111111/1/2021P1456 
1111111/1/2022P1444 
222221/1/2020P13334 
222221/1/2021P13433 
222221/1/2021p21212 
222221/1/2022P15666 
222221/1/2023P16555 
222221/1/2024P16655 
1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
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.

 

Jihwan_Kim_0-1708754024341.png

 

 

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.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

2 REPLIES 2
2016Cayenne
Frequent Visitor

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

Jihwan_Kim
Super User
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.

 

Jihwan_Kim_0-1708754024341.png

 

 

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.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.