cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SaudMeethal Regular Visitor
Regular Visitor

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

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.

13 REPLIES 13
Super User
Super User

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.

SaudMeethal Regular Visitor
Regular Visitor

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

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

SaudMeethal Regular Visitor
Regular Visitor

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

Super User
Super User

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.

lianpan Occasional Visitor
Occasional Visitor

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

re

SaudMeethal Regular Visitor
Regular Visitor

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.

Super User
Super User

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.

SaudMeethal Regular Visitor
Regular Visitor

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?