LOOKUPVALUE if date difference is smaller than two years

[babajat]

Good morning,

I need help with a problem that has been bugging me for days.

These are my data:

ID	DATE A	DATE D	I1	I2	I3
AA	30/01/2017	01/02/2017	FFF		ZZZ
BB	24/11/2021	24/11/2021		GGG
CC	22/03/2016	22/03/2016		HHH
DD	02/10/2018	02/10/2018	PPP	YYY
EE	06/08/2019	06/08/2019	JJJ
AA	21/08/2018	21/08/2018	LLL
XX	03/11/2020	03/11/2020		MMM	XYZ
CC	24/09/2019	24/09/2019	NNN
FF	20/02/2018	20/02/2018		BBB
XX	25/05/2021	25/05/2021		VVV
NN	31/10/2019	31/10/2019	CCC	ZZZ
MM	11/06/2019	11/06/2019		SSS
HH	09/11/2018	09/11/2018	CCC	QQQ
MM	26/11/2019	26/11/2019			UUU
MM	26/02/2021	27/02/2021			RRR
UU	26/07/2019	26/07/2019		TTT
PP	18/12/2018	18/12/2018	YYY
YY	22/04/2022	22/04/2022		III	LLL
TT	07/03/2018	07/03/2018	VVV	CCC
EE	01/10/2022	01/11/2022	CCC	XXX

I have 2 questions:

- QUESTION 1: I need a new calculated column that returns 1 if there is an entry with the same ID with a DATE D dating back less than 2 years (730 days) from the DATE A of interest;

- QUESTION 2: I need another new calculated column that returns 1 if there is an entry that meets the criteria of QUESTION 1 and also has the values ​​"XYZ" OR "UUU" in any column between I1, I2 OR I3.

Here's the expected result:

ID	DATE A	DATE D	I1	I2	I3	QUESTION 1	QUESTION 2
AA	30/01/2017	01/02/2017	FFF		ZZZ
BB	24/11/2021	24/11/2021		GGG
CC	22/03/2016	22/03/2016		HHH
DD	02/10/2018	02/10/2018	PPP	YYY
EE	06/08/2019	06/08/2019	JJJ
AA	21/08/2018	21/08/2018	LLL			1
XX	03/11/2020	03/11/2020		MMM	XYZ
CC	24/09/2019	24/09/2019	NNN
FF	20/02/2018	20/02/2018		BBB
XX	25/05/2021	25/05/2021		VVV		1	1
NN	31/10/2019	31/10/2019	CCC	ZZZ
MM	11/06/2019	11/06/2019		SSS
HH	09/11/2018	09/11/2018	CCC	QQQ
MM	26/11/2019	26/11/2019			UUU	1
MM	26/02/2021	27/02/2021			RRR	1	1
UU	26/07/2019	26/07/2019		TTT
PP	18/12/2018	18/12/2018	YYY
YY	22/04/2022	22/04/2022		III	LLL
TT	07/03/2018	07/03/2018	VVV	CCC
EE	01/10/2022	01/11/2022	CCC	XXX

I hope I was clear, thanks in advance.

[Ashish_Mathur]

Hi,

These calculated column formulas work

Column = if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[DATE d]>=EARLIER(Data[DATE A])-730&&Data[DATE D]<=EARLIER(Data[DATE A])))>1,1,BLANK())

Column 2 = if(Data[Column]=1,CALCULATE(COUNTROWS(Data),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[DATE d]>=EARLIER(Data[DATE A])-730&&Data[DATE D]<EARLIER(Data[DATE A])&&(Data[I1]="XYZ"||Data[I1]="UUU"||Data[I2]="XYZ"||Data[I2]="UUU"||Data[I3]="XYZ"||Data[I3]="UUU"))),BLANK())

Hope this helps.

[v-yueyunzh-msft · Accepted solution]

Hi , @babajat 

According to your description, you want to create two calculated columns from your logic. Right?

Here are the steps you can refer to :
(1)This is my test data:

(2)We can click "New Column" and enter this:

Q1 = var _cur_ID = [ID] 
var _cur_DATE_A = [DATE A] 
var _cur_DATE_D = [DATE D] 
var _pre_dateA  =MAXX( FILTER( 'Table' , 'Table'[ID] =_cur_ID && 'Table'[DATE A] < _cur_DATE_A) , [DATE A])
var _days = IF(_pre_dateA <> BLANK() , INT(_cur_DATE_D-_pre_dateA))
return
IF(_days <= 730 && _days<> BLANK(), 1, 0)

Q1 = var _cur_ID = [ID] 
var _cur_DATE_A = [DATE A] 
var _cur_DATE_D = [DATE D] 
var _pre_dateA  =MAXX( FILTER( 'Table' , 'Table'[ID] =_cur_ID && 'Table'[DATE A] < _cur_DATE_A) , [DATE A])
var _days = IF(_pre_dateA <> BLANK() , INT(_cur_DATE_D-_pre_dateA))
return
IF(_days <= 730 && _days<> BLANK(), 1, 0)

(3)Then we can meet your need , the result is as follows:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Thejeswar]

Hi @babajat,

You can use the following measures

Question 1 = VAR SELECTID = Table2[ID]

VAR MAXDATE = CALCULATE(MAX(Table2[DATE A]), FILTER(Table2, Table2[ID] = SELECTID))

VAR MINDATE = CALCULATE(MIN(Table2[DATE D]), FILTER(Table2, Table2[ID] = SELECTID))

RETURN

IF(INT(MAXDATE - MINDATE) > 730, 1, 0)

Question 2 = IF(Table2[Question 1] = 1 && (Table2[I1] IN {"XYZ", "UUU"} || Table2[I2] IN {"XYZ", "UUU"} || Table2[I3] IN {"XYZ", "UUU"}), 1, 0)

The Output looks like shown below

[babajat]

Hi! Thanks for the answer. I'm afraid that your measure is returning 1 if the difference is grater than 730 days instead of smaller. If i swap the ">730" with "<730" it stops working correctly for some reason (it seems that it stops filtering by ID) 😵

Furthermore, using this measure returns "1" on both the most recent and oldest entries, is there a way to highlight only the most recent?

[Thejeswar]

Hi @babajat ,

My bad!! This should be created as a Column and not a measure. But mentioned it as measure by mistake.

Kindly create the Columns as shown above. That is returning the right values when < 730 is mentioned in the logic. Attached below the screenshot

Regards,