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

[Anonymous]

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!

[Anonymous]

re

[Ashish_Mathur]

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.

[Anonymous]

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?

[Ashish_Mathur]

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.

[Anonymous]

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

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

Please review.

Thanks

[Ashish_Mathur]

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.

[Anonymous]

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.

[Ashish_Mathur]

I just checked.  The maximum number of times any Material Code appears is 2.  So my statement above is correct and the formula should return blanks for all Material codes.

[Ashish_Mathur]

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.

[Anonymous]

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?

[Ashish_Mathur]

This is extremenly confusing and absolutely not what you stated in the original post.  If you want my help, share a small simple exampel and show the expected result for that simple example.

[Anonymous]

Sorry about the confusion. 

Here is a sample explanation:

 

The repeated customers are highlighted with a unique colour for each.

When the customer appeared for the second time, his previous date of occurence is stored in the 'Previous Date' column.

Else it will always be blank. 

In the original post too, I meant that the first occurence should have the new column as blank, the rest should have the previous occurence date in the the new column, just mentioned it the other way around.

[Ashish_Mathur · Accepted solution]

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.