cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Find the date of the previous occurrence of the id and paste in the new column of that row.

Hi, 

 

The data consists of customer transactions.

For each row:

  • using the customer id, I need to access  the date of the previous occerence of the same customer id.
  • If it's the first occurence:
    • then the previous_date column should be blank().

         Else:

                 This date should be stored in a new column called "previous_date" of the same row.

 

It seemed complicated to implement, any way this is possible?

 

You can find the sample data set here: Sample Dataset

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Find the date of the previous occurrence of the id and paste in the new column of that row.

Hi,

 

Try this calculated column formula

 

=CALCULATE(MAX(Data[Date of Disbursement]),FILTER(Data,Data[Member Code]=EARLIER(Data[Member Code])&&Data[Date of Disbursement]<EARLIER(Data[Date of Disbursement])))

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

13 REPLIES 13
Highlighted
Super User IV
Super User IV

Re: Find the date of the previous occurrence of the id and paste in the new column of that row.

Hi,

 

Try this calculated column formula

 

Previos date = if(CALCULATE(MAX(Data[Date]),FILTER(Data,Data[Customer Id]=EARLIER(Data[Customer Id])&&Data[Date]<EARLIER(Data[Date])))=CALCULATE(min(Data[Date]),FILTER(Data,Data[Customer Id]=EARLIER(Data[Customer Id])&&Data[Date]<EARLIER(Data[Date]))),BLANK(),CALCULATE(MAX(Data[Date]),FILTER(Data,Data[Customer Id]=EARLIER(Data[Customer Id])&&Data[Date]<EARLIER(Data[Date]))))

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Helper II
Helper II

Re: Find the date of the previous occurrence of the id and paste in the new column of that row.

Hi Ashish,

Thanks for reverting.

 

I tried out your solution but the result  consist of blank() only.

Can you try it out on the sample data whether its working for you?

 

Highlighted
Super User IV
Super User IV

Re: Find the date of the previous occurrence of the id and paste in the new column of that row.

Hi,

 

It works fine - some entries in that column are blank but a lot are filled up.  You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Helper II
Helper II

Re: Find the date of the previous occurrence of the id and paste in the new column of that row.

For some reason, I only get blanks when I replicate the formula for my use case. Can't figure out why.

pbi_community.png

 

 

 

I've stripped down my actual data with actual coulmn names in this pbix. The formula you provided is also implemented.

Please review.

 

Thanks

Highlighted
Super User IV
Super User IV

Re: Find the date of the previous occurrence of the id and paste in the new column of that row.

Hi,

 

There is no mistake with your formula.  Looks like the data is such that the prior date for every Member Code is also the first date for that customer code so that is why you see a blank.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Anonymous
Not applicable

Re: Find the date of the previous occurrence of the id and paste in the new column of that row.

re

Highlighted
Helper II
Helper II

Re: Find the date of the previous occurrence of the id and paste in the new column of that row.

Hi,

 

Many customers have only one record, but there are thousands of other customers who appear twice at max. So atleast for the second appearance their previous date should be there.

Highlighted
Super User IV
Super User IV

Re: Find the date of the previous occurrence of the id and paste in the new column of that row.

So, for customers who appear twice at max, the result in the previous date column should be blank because the previous date will be the first date.  There can be an entry in the earlier date column only when there are more than 2 entries for that customer.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Helper II
Helper II

Re: Find the date of the previous occurrence of the id and paste in the new column of that row.

Oh, the use case that I have requires the previous date even if there is only one record above it.  

 

Any possible work around for this?

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors