You can also do this in the front end using DDIFF similarly to the way Ido suggests... flagging rows where the first date is greater than the second date...
IF ( MIN ( DDIFF ( [Days in First Date] , [Days in Second Date] ) ) > 0 , 1 , 0 )
There are a few ways to achieve this, both would require creating custom columns in the EC table, or creating a new side table to assist with this. You could use the CASE/WHEN but the condition would require int values that represent the dates, or by creating a measured value, that its filter to a flag that applies only to rows which the dates fall into your needed condition.
I would go about this by either of the below options
Create a custom column in the EC table that compares the 2 dates using DAYDIFF(end,start) see this link . If you need to know how many days in between then just use that function, if you only need to know if date 1 is after date 2 then create a CASE/WHEN comparing the 2 dates and just give a 1 or 0 depending on your logic. Then wither use the actual numeric date difference in a CASE/WHEN statement and the condition should check if the new custom columns value is positive or negative. If you end up getting the binary value, just use that in a measured value, and filter to the 1's or 0's accordingly.
Create a custom column in the EC table that converts the dates to an int representation with the formula below Integer (e.g., YYYYMMDD): getyear(Date)*10000 + getmonth(Date)*100 + getday(Date). Then use these int values to compare the dates and see if they end up positive or negative in your formula. Check out this article
I’m following up to see if the solutions offered by wallingfordce or Ido_QBeeQ worked for you.
If so, please click the 'Accept as Solution' button on the appropriate post. That way others with the same questions can find the answer. If not, please let us know so that we can continue to help.
