I thought this would be really simple, but it is proving to be a serious nusance. All I want to do is determine if a date in one column of my table is within the last 12 months. I need this in a calculated column. It
=dateadd(InitialStudyLoad[TODAYDate],-1,year)
will eventually be used in an IF statement to assign a category, but for now, I'm breaking it into steps to try to find out where the issue is. The column of interest is TransDate. If the TransDate is between TODAY-1 Year and TODAY I will call that customer ACTIVE. Otherwise something else.
So first, I just tried to create a column that calculated the date for one year ago from today using
=dateadd(today(),-1,year)
That gave an error stating the first argument had to be a column. So I created a [helper] column that just generates today's date [=TODAY()] and named it TODAYDate. I used this new column in my calculation as such
=dateadd(InitialStudyLoad[TODAYDate],-1,year)
No error this time but the column generates nothing but BLANKS! I cannot understand why this is so difficult. I just need a column that generates a "flag" such that if the transaction date is within the last year from today, mark them as "ACTIVE". This column will be used as a slicer. I'm sure it is something small and silly in my syntax but it is just not clicking for me today. What am I doing wrong?
NOTE:
In plain old Excel, if I use =IF(TODAY()-A1<=364,"ACTIVE","NOT ACTIVE") where A1 is the TransDate, works like a charm. The same formula in a calculated column also works replacing A1 with "InitialStudyLoad[TransDate]". How can I do this same simple calculation in DAX without specifying the exact number of days (i.e. "minus one year")? Thanks.
