Forum Discussion
Finding records after a certain date
Hi ConstMoss,
You can firstly add an index column into your current table in Query Editor, then create calculated columns using the DAX below.
newEvalDate = IF(Table2[Customer]=LOOKUPVALUE(Table2[Customer],Table2[Index],Table2[Index]-1) && Table2[Product]=LOOKUPVALUE(Table2[Product],Table2[Index],Table2[Index]-1)&& ISBLANK(Table2[EvalDate]),LOOKUPVALUE(Table2[EvalDate],Table2[Index],Table2[Index]-1),Table2[EvalDate])
flag = IF(Table2[OrderDate]>Table2[newEvalDate],"Yes","No")
Thanks,
Lydia Zhang
Hi ConstMoss,
You can firstly add an index column into your current table in Query Editor, then create calculated columns using the DAX below.
newEvalDate = IF(Table2[Customer]=LOOKUPVALUE(Table2[Customer],Table2[Index],Table2[Index]-1) && Table2[Product]=LOOKUPVALUE(Table2[Product],Table2[Index],Table2[Index]-1)&& ISBLANK(Table2[EvalDate]),LOOKUPVALUE(Table2[EvalDate],Table2[Index],Table2[Index]-1),Table2[EvalDate])
flag = IF(Table2[OrderDate]>Table2[newEvalDate],"Yes","No")
Thanks,
Lydia Zhang
Is there a way to count the number of orders that ocurred after the Eval date if the Eval date is in a different table?
Ex:
| Order table | ||
| Customer | Product | OrderDate |
| 123 | ABC | 2/1/2017 |
| 123 | ABC | 1/15/2017 |
| 456 | CDE | 2/1/2017 |
| 456 | CDE | 2/16/2017 |
| 456 | CDE | 3/15/2017 |
| Customer table | ||
| Customer | EvalDate | |
| 123 | 1/15/2017 | |
| 456 | 2/15/2017 |