Calculated Column for one year from today

domtrump — Mon, 19 Dec 2022 22:07:43 GMT

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.

Re: Calculated Column for one year from today

vicky_ — Tue, 20 Dec 2022 00:02:29 GMT

Try:

IF(DATEDIFF(TODAY(),[Specific Date], DAY) <= 364,"ACTIVE","NOT ACTIVE")

Re: Calculated Column for one year from today

FreemanZ — Tue, 20 Dec 2022 00:15:35 GMT

hi domtrump

Try to add a column with this:

Status1=

IF(

[Date]>=EDATE(TODAY(), -12),

"Yes",

"No"

)

Status2=

IF(

[Date]>=DATE( YEAR(TODAY())-1, MONTH(TODAY()), DAY(TODAY())),

"Yes",

"No"

)

DATEADD is Time Intelligence function, which always replies on dedicated Date Tabke.

topic Re: Calculated Column for one year from today in DAX Commands and Tips

Calculated Column for one year from today

Re: Calculated Column for one year from today

Re: Calculated Column for one year from today