Forum Discussion

Lucie_C's avatar
Lucie_C
Frequent Visitor
8 years ago
Solved

Value previous year

Hello,

please, can you help me. I have simple data table and I need to add Calculated coloum Value previous year:

 

 

Please, anyone does know DAX formula?

Thank you

  • v-piga-msft's avatar
    v-piga-msft
    8 years ago

    Hi Lucie_C,

     

    You could create the calculated column with the formula below.

     

    Column =
CALCULATE (
    MAX ( 'Table1'[Value] ),
    FILTER (
        'Table1',
        YEAR ( 'Table1'[Date] )
            = YEAR ( EARLIER ( 'Table1'[Date] ) ) - 1
            && [Company] = EARLIER ( Table1[Company] )
    )
)

    Here is your desired output.

     

     

    Hope this can help you!

     

    Best Regards,

    Cherry

4 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    8 years ago

    Hi Lucie_C,

     

    You could create the calculated column with the formula below.

     

    Column =
CALCULATE (
    MAX ( 'Table1'[Value] ),
    FILTER (
        'Table1',
        YEAR ( 'Table1'[Date] )
            = YEAR ( EARLIER ( 'Table1'[Date] ) ) - 1
            && [Company] = EARLIER ( Table1[Company] )
    )
)

    Here is your desired output.

     

     

    Hope this can help you!

     

    Best Regards,

    Cherry

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hello,

       

      I have the same case but with a second date column called "As of date", which will be selected as a filter in the visual.

       

      How can I expand the formula to include that second date?

       

      Thank you!

  • GDO's avatar
    GDO
    Regular Visitor
    6 years ago

    You could also use the formula below:

     

    Coloumn = LOOKUPVALUE( Table1[Value] ; Table1[Company] ; Table1[Company] ; Table1[Date] ; SAMEPERIODLASTYEAR( Table1[Date] ) )