Forum Discussion

Helper I

6 years ago

Solved

Most recent previous date

HI I have a table that looks something like this

Person ID	Date
A	12/31/2019
A	12/1/2019
A	10/2/2019
A	6/1/2019
B	12/10/2019
B	10/1/2019
C	6/12/2019
C	3/1/2019
C	2/19/2019

For each row, I want to calculate what the previous date was, for that person.

For example:

Person ID	Date	Previous
A	12/31/2019	12/1/2019
A	12/1/2019	10/2/2019
A	10/2/2019	6/1/2019
A	6/1/2019
B	12/10/2019	10/1/2019
B	10/1/2019
C	6/12/2019	3/1/2019
C	3/1/2019	2/19/2019
C	2/19/2019

How can I do this?

Anonymous
6 years ago
Previous date =
var date_ = 'Table'[Date]
var ID_ = 'Table'[Person ID]
return
CALCULATE(MAX('Table'[Date]);FILTER(ALL ('Table') ; 'Table'[Person ID] = ID_ && 'Table'[Date] < date_))

3 Replies

littlemojopuppy
Community Champion
6 years ago
Hi! Take a look at this
Anonymous
Not applicable
6 years ago
Previous date =
var date_ = 'Table'[Date]
var ID_ = 'Table'[Person ID]
return
CALCULATE(MAX('Table'[Date]);FILTER(ALL ('Table') ; 'Table'[Person ID] = ID_ && 'Table'[Date] < date_))
amitchandak
Super User
6 years ago
In case you want a column
new column = maxx(filter(table, table[personID]=earlier(table[personID]) && table[Date]<earlier(table[Date])),table[Date])

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin