Forum Discussion
Calculated Column for one year from today
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.
Try:
IF(DATEDIFF(TODAY(),[Specific Date], DAY) <= 364,"ACTIVE","NOT ACTIVE")hi domtrump
Try to add a column with this:Status1=IF([Date]>=EDATE(TODAY(), -12),"Yes","No")orStatus2=IF([Date]>=DATE( YEAR(TODAY())-1, MONTH(TODAY()), DAY(TODAY())),"Yes","No")DATEADD is Time Intelligence function, which always replies on dedicated Date Tabke.
2 Replies
- vicky_
Super User
Try:
IF(DATEDIFF(TODAY(),[Specific Date], DAY) <= 364,"ACTIVE","NOT ACTIVE")