Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
bml123
Post Patron
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.

 

DateLast_end_of_month_in_year
01/01/2020N
 
31/12/2020Y
01/01/2021N
 
31/12/2021Y
01/01/2022N
 
31/08/2022N
01/09/2022N
30/09/2022Y
01/10/2022N
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

@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"
  )

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler ,

 

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?

@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"
  )

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.