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
Super UserHi 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.aspx
If 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?