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.

Post Patron

## Get last month date in the year

Hi,

I have a calendar table and I want to add a flag for last month end date in the year until today.

 Date Last_end_of_month_in_year 01/01/2020 N … 31/12/2020 Y 01/01/2021 N … 31/12/2021 Y 01/01/2022 N … 31/08/2022 N 01/09/2022 N 30/09/2022 Y 01/10/2022 N 02/10/2022

For 2020, 31/12/2020, for 2021, 31/12/2021 and for 2022, 30/09/2022 should be the last month end dates in the years.

How do I achieve this?

1 ACCEPTED SOLUTION
Super User

@bml123 So maybe:

``````Column =
VAR __EOM = EOMONTH(TODAY(),-1)
RETURN
SWITCH(TRUE(),
YEAR([Date]) = YEAR(TODAY()) && [Date] = __EOM,"Y",
[Date] = DATE(YEAR([Date]),12,31),"Y",
"N"
)``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
3 REPLIES 3
Super User

@bml123 So like EOMONTH(TODAY(),-1) ?

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Post Patron

No, not like that. I have edited my question to make it more clear.

For 2020, 31/12/2020, for 2021, 31/12/2021 and for 2022, 30/09/2022 should be flagged as the last month end dates in the years.

How do I achieve this?

Super User

@bml123 So maybe:

``````Column =
VAR __EOM = EOMONTH(TODAY(),-1)
RETURN
SWITCH(TRUE(),
YEAR([Date]) = YEAR(TODAY()) && [Date] = __EOM,"Y",
[Date] = DATE(YEAR([Date]),12,31),"Y",
"N"
)``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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