Forum Discussion

2016Cayenne's avatar
2016Cayenne
Frequent Visitor
2 years ago
Solved

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 
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

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

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    2 years ago

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