identify policies within last 12 months

[Anonymous]

Hi,

I have a table which contains all live policies from our database, which has a column with the policy craete date.  I am trying to create a column that identifies whether a policy was created within 12 months from today's date. 

In short, I need to see if a policy was created within the last 12 months, then this is classed as 'New Business', if not, then this will remain blank.

Is this possible?

Thanks.

[Phil_Seamark · Accepted solution]

Hi @Anonymous

You can add a calculated column in yoru policy table along the following lines

Policy Live =IF('Table1'[Date]>TODAY()-365,1,0)

Which you can then use as a filter.  You don't have to use 1/0 either.  You can subsitute with text if you prefer "live" / "not live"

or use the BLANK fuction instead of the 0

[v-sihou-msft · Accepted solution]

@Anonymous

You can also try Power Query approach. Just add a custom column using Date.AddMonths() function in Query Editor.

Regards,

[Phil_Seamark · Accepted solution]

Hi @Anonymous

You can add a calculated column in yoru policy table along the following lines

Policy Live =IF('Table1'[Date]>TODAY()-365,1,0)

Which you can then use as a filter.  You don't have to use 1/0 either.  You can subsitute with text if you prefer "live" / "not live"

or use the BLANK fuction instead of the 0

[spuder]

Hi,

you can try to use Power Query for that job.

There is an equivalent in DAX for that.

calculated column 

check = if(DATEADD([createdate],-1,year)>=1,"new business","")