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

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

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.


Go to My LinkedIn Page


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.


Go to My LinkedIn Page


Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors