Forum Discussion
Helper IFinding 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 tips on how to do this would be appreciated..
For example -
1. the first row below would have the flag as yes because the customer placed the order after the eval date
2. the second row would have the **bleep** as no because this order is prior to eval date
3. the 3rd row would be no
4. the 4th row would be yes
| Customer | Product | EvalDate | OrderDate |
| 123 | ABC | 01/15/2017 | 02/01/2017 |
| 123 | ABC | 01/15/2017 | 01/14/2017 |
| 456 | CDE | 02/15/2017 | 02/01/2017 |
| 456 | CDE | 02/15/2017 | 02/16/2017 |
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
7 Replies
- GilbertQ
Super User
Hi ConstMoss
When using the Calculated Column in DAX you could use the following:
Difference = DateDiff([OrderDate]-[EvalDate],Day)
That will then give you the difference in days.
Reference: https://msdn.microsoft.com/en-us/library/dn802538.aspx?f=255&MSPPError=-2147217396
You could then create another Calculated Column which would check to see if the number is positive or negative, and then based on that could give you your Yes or No
Is Before Eval = IF(SIGN([Difference]) = -1, "Yes","No")
This will then return your data as required.
Reference: https://msdn.microsoft.com/en-us/library/ee634249.aspxIf you really wanted to, you could put it all into one column, as shown below. I advise people to rather do the columns in logical steps, makes it less complex as well as easier to troubleshoot if something is incorrect.
Is Before Eval = IF(SIGN(DateDiff([OrderDate]-[EvalDate],Day)) = -1, "Yes","No")
ConstMossthanks @guavaq, one thing i forgot to mention is that i have rows in my table where there is no eval date, in which case i have to grab the eval date based on prior records for that customer and product combination.
For exaple, for the last row in my data below, i'll need to grab the en date from one of the previous recrods.
Customer Product EvalDate OrderDate 123 ABC 01/15/2017 02/01/2017 123 ABC 01/15/2017 01/15/2017 456 CDE 02/15/2017 02/01/2017 456 CDE 02/15/2017 02/16/2017 456 CDE 03/15/2017 - GilbertQ
Hi ConstMoss
Just a question in terms of are you always wanting to look at this data on a row level, or aggregated it up?
The reason for the question is if it is being aggregated up, the DAX that is used could be different?
Also how difficult would it be to get the data added if blank from your Source system?
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...
