Forum Discussion

ConstMoss's avatar
ConstMoss
Helper I
9 years ago
Solved

Finding records after a certain date

Hello everyone - i have the following sample data and am trying to figure out how to create a flag (calculated column) to know if a given order was placed after the evaluation date has ended. any tip...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

samk's avatar
samk
Regular Visitor
9 years ago

I'd create two columns: 

1) A 'Custom Column':

col1=[OrderDate]-[EvalDate] 


This will give you the number of dates after the EvalDate that the order was placed. 

2) A 'Conditional Column': If col2=col1 > 0, then 'Yes'

This will flag the row as 'Yes' if the Order Date is greater than the eval date. 

The Query Editor will remember the steps you took, so you can delete col1 after if you want. 

I'm sure you could accomplish this in one custom column using DAX, but I dunno how...