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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
Super User
Super User

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
Super User
Super User

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors