New Column based on criteria in two different rows for the same ID

[amirghaderi]

Hi,

I want to add a new column for the below table and the value for the column to be based on the below criteria.

For Code A1, A2,A3,...

If in Criteria column MDR Planned Finish date < 28-Oct AND it has a criteria of MDR Forecast date and Forecast date > 28-Oct, the the NEW column value to be Overdue,

If in Criteria column MDR Planned Finish date > 28-Oct AND it has a criteria of MDR Forecast date and Forecast date > 28-Oct, the the NEW column value to be Not Overdue,

If in Criteria column is MDR Issued Date, then the ew column to be "issued.

Code	MDR Description	Criteria	Date	Status	New Column
A100	A1	MDR Planned Finish Date	28-Oct-20	Planned by cut off
A100	A1	MDR Forecast Date	05-Nov-20	Not Issued	Overdue
A200	B1	MDR Planned Finish Date	10-Nov-20	Planned for Future
A200	B1	MDR Forecast Date	15-Nov-20	Not Issued	Not Overdue
A300	C1	MDR Planned Finish Date	10-Nov-20	Planned for Future
A300	C1	MDR issued Date	27-Oct-20	Issued	Issued

[ryan_mayu]

@amirghaderi 

you can try this to create a column.

please note i slightly changed your creteria.

If in Criteria column MDR Planned Finish date < =28-Oct AND, otherwise A1 will be blank instead of "overdue".

Column = if('Table'[Criteria]="MDR Forecast Date"&& 'Table'[Date]>date(2020,10,28)&&maxx(FILTER('Table','Table'[MDR Description]=EARLIER('Table'[MDR Description])&&'Table'[Criteria]="MDR Planned Finish Date"),'Table'[Date])<=date(2020,10,28),"Overdue",if('Table'[Criteria]="MDR Forecast Date"&& 'Table'[Date]>date(2020,10,28)&&maxx(FILTER('Table','Table'[MDR Description]=EARLIER('Table'[MDR Description])&&'Table'[Criteria]="MDR Planned Finish Date"),'Table'[Date])>date(2020,10,28),"NotOverdue",if('Table'[Criteria]="MDR issued Date","Issued")))

[amirghaderi]

Hi Ryan,

Getting very close now. Thanks for the reply.

Now how can I get Overdue, not overdue, issued values repeated for each record of A1, B1 C,... and not having blank lines on the new column.

Thanks,

Amir

[ryan_mayu · Accepted solution]

@amirghaderi 

you can try this. The DAX is a little bit long. Hope the logic is correct for your real data.

Column = if(ISBLANK(maxx(FILTER('Table','Table'[MDR Description]=EARLIER('Table'[MDR Description])&&'Table'[Criteria]="MDR issued Date"),'Table'[Criteria])),if(MAXX(FILTER('Table','Table'[MDR Description]=EARLIER('Table'[MDR Description])&&'Table'[Criteria]="MDR Planned Finish Date"),'Table'[Date])<=DATE(2020,10,28)&&MAXX(FILTER('Table','Table'[MDR Description]=EARLIER('Table'[MDR Description])&&'Table'[Criteria]="MDR Forecast Date"),'Table'[Date])>date(2020,10,28),"Overdue",if(MAXX(FILTER('Table','Table'[MDR Description]=EARLIER('Table'[MDR Description])&&'Table'[Criteria]="MDR Planned Finish Date"),'Table'[Date])>DATE(2020,10,28)&&MAXX(FILTER('Table','Table'[MDR Description]=EARLIER('Table'[MDR Description])&&'Table'[Criteria]="MDR Forecast Date"),'Table'[Date])>date(2020,10,28),"Not Overdue")),"issued")

[amirghaderi]

Thanks for your help, It worked as expected.

[amitchandak]

@amirghaderi , Trya new column like

New column =
var _Planned = maxx(filter(Table, [Code] =earlier([Code]) && [Criteria] = "MDR Planned Finish Date"),[Date])
var _Forecast = maxx(filter(Table, [Code] =earlier([Code]) && [Criteria] = "MDR Forecast Date"),[Date])
var _issued = maxx(filter(Table, [Code] =earlier([Code]) && [Criteria] = "MDR issued Date"),[Date])
return
if(Status in {" Not Issued","Issued" },
Switch( True(),
_Planned< today() && _Forecast>today() , "Overdue",
_Planned> today() && _Forecast>today() , "Not Overdue",
not(isblank(_issued )) , "Issued"
)
, blank())