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
67nmark
Helper I
Helper I

DAX calculated column- get previous record value

Hello, I've looked for answers but can't get what I want. Essentially I need a column that brings the value associated with a previous date onto the current row. The image below shows the requirement. I would like the Tester column to show the previous date's value for each person. The DAX I have has a hard coded date that works for a the most recent dates but as I go down the column it (obviously) starts failing. Any help much appreciated.previous.png

1 ACCEPTED SOLUTION
FarhanAhmed
Community Champion
Community Champion

Try this

 

Last Val = CALCULATE(LASTNONBLANK('Table'[Value],'Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Name]),'Table'[Date]<EARLIER('Table'[Date])))






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

Proud to be a Super User!




View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

@67nmark See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
FarhanAhmed
Community Champion
Community Champion

Try this

 

Last Val = CALCULATE(LASTNONBLANK('Table'[Value],'Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Name]),'Table'[Date]<EARLIER('Table'[Date])))






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

Proud to be a Super User!




Perfect. Thanks so much 🙂

amitchandak
Super User
Super User

@67nmark , As a measure with date table

Last Day Non Continuous = CALCULATE(Table[Value],filter(ALLSELECTED('Date'),'Date'[Date] =MAXX(FILTER(ALLSELECTED('Date'),'Date'[Date]<max('Date'[Date])),'Date'[Date])))

 

as a column

Last day value =
var _max = maxx(filter(Table, [date] < earlier([date]) && [name] = earlier([name])),[date])
return
maxx(filter(Table, [date] = _max && [name] = earlier([name])),[value])

 

same is discussed in

https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...

 

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.