Forum Discussion
Helper IFinding 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
Helper IGilbertQ - We won't be aggregating it, even if we did, we always look at details. the data in the source system is populated just for the row where the eval date ends.
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
