Forum Discussion

DouglasRoubaud's avatar
DouglasRoubaud
Frequent Visitor
4 years ago
Solved

Create Column - Previous Year (Dax or Power Query)

I have a base with 3 years of history and I would like to create a column with the same period of the previous year... Aiming to hit line by line (of the same date), without having to place the date ...
Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    4 years ago

    Hi  DouglasRoubaud ,

    Tty to use the following dax to create a new column:

    previousvalue = 
CALCULATE (
    MAX ( 'Table'[Value] ),
    FILTER (
        ALL ( 'Table' ),
        YEAR ( 'Table'[Date] )
            = YEAR ( EARLIER ( 'Table'[Date] ) ) - 1
            && MONTH ( 'Table'[Date] ) = MONTH ( EARLIER ( 'Table'[Date] ) )
            && DAY ( 'Table'[Date] ) = DAY ( EARLIER ( 'Table'[Date] ) )
    )
)

    Get:

    Final create visual:

    Did I answer your question? Mark my post as a solution!


    Best Regards

    Lucien

v-luwang-msft's avatar
v-luwang-msft
Community Support
4 years ago

Hi  DouglasRoubaud ,

Tty to use the following dax to create a new column:

previousvalue = 
CALCULATE (
    MAX ( 'Table'[Value] ),
    FILTER (
        ALL ( 'Table' ),
        YEAR ( 'Table'[Date] )
            = YEAR ( EARLIER ( 'Table'[Date] ) ) - 1
            && MONTH ( 'Table'[Date] ) = MONTH ( EARLIER ( 'Table'[Date] ) )
            && DAY ( 'Table'[Date] ) = DAY ( EARLIER ( 'Table'[Date] ) )
    )
)

Get:

Final create visual:

Did I answer your question? Mark my post as a solution!


Best Regards

Lucien