cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

Microsoft

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

3 REPLIES 3
Microsoft

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

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

Helper V

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

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors