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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
ClemFandango
Advocate II
Advocate II

Date table for last day of each month

Hello all,

 

I think this is quite an easy one, but I am still struggling with it.

 

I would like the MMYYDD column to represent the last day of each month - it is currently showing the 1st.

 

This is my code so far:-

 

 

 

Dates2 =
 GENERATE ( CALENDAR(
 DATE ( YEAR ( MIN ( 'date'[Start Date] ) ), 1, 1 ),
 DATE ( YEAR ( MAX ( 'date'[End Date] ) ), 12, 1 )),
 VAR currentDay = [Date]
 VAR month = MONTH ( currentDay )
 VAR year = YEAR ( currentDay )
 RETURN ROW (
 "MMYYDD", DATE(year,month,01)
 ))

 

Any thoughts gleefully welcomed!

 

DateMMYYDD
01/01/2018 00:0001/01/2018 00:00
02/01/2018 00:0001/01/2018 00:00
03/01/2018 00:0001/01/2018 00:00
04/01/2018 00:0001/01/2018 00:00
05/01/2018 00:0001/01/2018 00:00
06/01/2018 00:0001/01/2018 00:00
07/01/2018 00:0001/01/2018 00:00
08/01/2018 00:0001/01/2018 00:00
09/01/2018 00:0001/01/2018 00:00
10/01/2018 00:0001/01/2018 00:00
1 ACCEPTED SOLUTION
ERD
Community Champion
Community Champion

Hi @ClemFandango ,

You can use 

ROW ( "MMYYDD", EOMONTH( [Date], 0 ) )

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

View solution in original post

3 REPLIES 3
ClemFandango
Advocate II
Advocate II

Thank you @FreemanZ  & @ERD  both excellent solutions!

FreemanZ
Super User
Super User

hi @ClemFandango 

to add a column for end of month of a date column, try like:
column = EOMONTH([date],0)

ERD
Community Champion
Community Champion

Hi @ClemFandango ,

You can use 

ROW ( "MMYYDD", EOMONTH( [Date], 0 ) )

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.