cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper V

## dynamic last 12 months column

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.

2 ACCEPTED SOLUTIONS
Super User

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.

Community Support

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.

2 REPLIES 2
Community Support

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.

Super User

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.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors