cancel
Showing results for
Did you mean:
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

## 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

## 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.

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?

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.

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.

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

Thanks

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.

Highlighted
Occasional Visitor

re

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

## 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.

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?