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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vega
Resolver III
Resolver III

Calculated Column Last Year Date

Hello,

 

I have a Date table and I want to create a calculated column that gives me the date of the previous year. For example, if the date is 1/1/2018 the LY column should be 1/1/2017. Are there any DAX functions that will accomplish this? When I use SAMEPERIODLASTYEAR or DATEADD, it returns a table, so it won't work with a calculated column. Also, if there is a function, how would it handle the leap year? What would the previous year column look like for 2/29/2016?

 

Thanks.

2 ACCEPTED SOLUTIONS
Zubair_Muhammad
Community Champion
Community Champion

HI @vega

 

Try this Column.

 

For 29 Feb 2016 it will give 1 Mar 2015

 

LY Date =
DATE ( YEAR ( DateTable[Date] ) - 1, MONTH ( DateTable[Date] ), DAY ( DateTable[Date] ) )

Regards
Zubair

Please try my custom visuals

View solution in original post

v-huizhn-msft
Employee
Employee

Hi @vega,

 Based on my understanding, if this year is the leap year, the previous year column should display blank. For example the previous year of 2/29/2016 day is blank, because there is no 2/29/2015. Please create a calculated column using the formula.

LY Date_Edit =
IF (
    MONTH ( DateTable[Date] ) = 2
        && DAY ( DateTable[Date] ) = 29,
    BLANK (),
    DATE ( YEAR ( DateTable[Date] ) - 1, MONTH ( DateTable[Date] ), DAY ( DateTable[Date] ) )
)


Best Reards,
Angelia

View solution in original post

3 REPLIES 3
v-huizhn-msft
Employee
Employee

Hi @vega,

 Based on my understanding, if this year is the leap year, the previous year column should display blank. For example the previous year of 2/29/2016 day is blank, because there is no 2/29/2015. Please create a calculated column using the formula.

LY Date_Edit =
IF (
    MONTH ( DateTable[Date] ) = 2
        && DAY ( DateTable[Date] ) = 29,
    BLANK (),
    DATE ( YEAR ( DateTable[Date] ) - 1, MONTH ( DateTable[Date] ), DAY ( DateTable[Date] ) )
)


Best Reards,
Angelia

Zubair_Muhammad
Community Champion
Community Champion

HI @vega

 

Try this Column.

 

For 29 Feb 2016 it will give 1 Mar 2015

 

LY Date =
DATE ( YEAR ( DateTable[Date] ) - 1, MONTH ( DateTable[Date] ), DAY ( DateTable[Date] ) )

Regards
Zubair

Please try my custom visuals

Hi @Zubair_Muhammad,

This works very well as a calculated column. I was wondering if this can also be used as a measure?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.