dynamic last 12 months column

[selpaqm]

I have a calender table and added this year and last year columns basically like 

this_year=if(date(date)>31/10/2020, "TY","")

unfurtunately I need to change  the date in formula manually. is there any way to create a column for last 12 months date to mark as TY? ie next month date need to be 30/11/2020... It might be easy but I could not find it in column formula and not want to use measure to create it because mostly people want to use show data point as table section.

[VahidDM · Accepted solution]

Hi @selpaqm 

Try this:
Var _EndMonth = EOMONTH(Today()) --return date in datetime format of the last day of the month based on today date

Var _LastY = Date(Year(_EndMonth)-1,month(_EndMonth),day(_EndMonth)) --Last year date

this_year=if(date(date)>=_LastY&&date(date)<=_EndMonth, "TY","")

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

[v-xiaotang · Accepted solution]

Hi @selpaqm 

You can use EOMONTH(TODAY(),-13) to replace the static date, i.e. 

this_year=if(date(date)>EOMONTH(TODAY(),-13), "TY","")

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[VahidDM · Accepted solution]

Hi @selpaqm 

Try this:
Var _EndMonth = EOMONTH(Today()) --return date in datetime format of the last day of the month based on today date

Var _LastY = Date(Year(_EndMonth)-1,month(_EndMonth),day(_EndMonth)) --Last year date

this_year=if(date(date)>=_LastY&&date(date)<=_EndMonth, "TY","")

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!