topic Re: Date table for last day of each month in DAX Commands and Tips

Date table for last day of each month

ClemFandango — Wed, 03 May 2023 13:31:22 GMT

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!

ERD — Wed, 03 May 2023 13:53:18 GMT

You can use

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

FreemanZ — Wed, 03 May 2023 13:53:56 GMT

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

ClemFandango — Wed, 03 May 2023 15:35:26 GMT

Thank you FreemanZ & ERD both excellent solutions!