cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## DAX that would give me last year's asset from this year's asset

Using 3 columns (Date, Name, This year asset), what DAX via "New column" (not via "New measure") would give me last year asset exactly as below? Please assume that 3 columns (Date, Name, This year asset) are all 'columns' too (not 'measures').

I want a New column named "Last year asset":

Last year asset = ?

 Date Name This year asset Last year asset 7/10/2023 Paul \$134 \$897 7/10/2022 Paul \$897 \$965 7/10/2021 Paul \$965 n/a 4/13/2021 Tony \$322 \$468 4/13/2020 Tony \$468 n/a 2/26/2024 Christine \$230 \$763 2/26/2023 Christine \$763 \$797 2/26/2022 Christine \$797 \$865 2/26/2021 Christine \$865 n/a 6/19/2023 Bella \$983 \$142 6/19/2022 Bella \$142 \$337 6/19/2021 Bella \$337 \$47 6/19/2020 Bella \$47 n/a

2 ACCEPTED SOLUTIONS
Solution Sage

hello @junding9

Create a calculated column with following DAX:

Last year asset =
var _LastYearDate = MAXX(FILTER('Table','Table'[Date]<EARLIER('Table'[Date])&&'Table'[Name]=EARLIER('Table'[Name])),'Table'[Date])
Return
CALCULATE(MAX('Table'[This year asset]),FILTER('Table','Table'[Name]=EARLIER('Table'[Name])&&'Table'[Date]=_LastYearDate))

Thank you.

Super User

 Expertise = List.Accumulate(        {Days as from Today},        {Skills and Knowledge},        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday))

2 REPLIES 2
Super User

 Expertise = List.Accumulate(        {Days as from Today},        {Skills and Knowledge},        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday))

Solution Sage

hello @junding9

Create a calculated column with following DAX:

Last year asset =
var _LastYearDate = MAXX(FILTER('Table','Table'[Date]<EARLIER('Table'[Date])&&'Table'[Name]=EARLIER('Table'[Name])),'Table'[Date])
Return
CALCULATE(MAX('Table'[This year asset]),FILTER('Table','Table'[Name]=EARLIER('Table'[Name])&&'Table'[Date]=_LastYearDate))

Thank you.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors