DAX: What is wrong with my EOMONTH formula?

[ah2020]

I have a model with a table called "MthYrTxt". It is not my date dimension (calendar) table but it has a "date" column, properly formatted as date.

The table looks as follows:

date	MthYr	SortOrder
2019-07-01	Jul 2019	1
2019-08-01	Aug 2019	2
2019-09-01	Sep 2019	3
2019-10-01	Oct 2019	4
2019-11-01	Nov 2019	5
2019-12-01	Dec 2019	6

I need a Measure that will give the last date of each date mentioned in the table above.

That is, 2019-07-01 as 2019-07-31, 2019-08-01 as 2019-08-31, 2019-09-01 as 2019-09-30 and so on.

I also need a second Measure that will give me the numbers of days for each month mentioned in that table: 31, 31 , 30 and so on.

I have tried these measures to get the last day of the Month but they are not working properly:

LastDayOfMonth = EOMONTH(MthyrTaxt[date])

LastDayOfMonth = ENDOFMONTH)(MthYrTxt[date)]

What am I doing wrong?

I am stuck with the Measure to get the number of days!

[PaulDBrown · Accepted solution]

@ah2020 

An alternative way to calculate the days in a month is:

- the ENDOFMONTH function returns the last date in the month in the date filter context.

- the DAY funtion returns the day number for the ENDOFMONTH date, so in effect it is the number of days in the month.

[TomMartens]

Hey @PaulDBrown ,

of course, I will try to change my habit 🙂

Never thought of this.

Regards,

Tom

[PaulDBrown]

@TomMartens 

Great to hear! It's pretty cool when you find that DAX has little gems hidden up its sleeve!

[TomMartens]

Hey @ah2020 , 

I used these DAX statements to create calculated columns:

Last Day of Month = EOMONTH('Table'[date] , 0)

Number of Days = DATEDIFF(DATE(YEAR('Table'[date]) , MONTH('Table'[date]) , 1) , 'Table'[Last Day of Month] , DAY) + 1

and these statements to create measures:

ms Last Day of Month = EOMONTH(MAX('Table'[date]) , 0)

ms Number of Days = 
var _year = YEAR(MAX('Table'[date]))
var _month = MONTH(MAX('Table'[date]))
var _yearLast = YEAR(MAX('Table'[Last Day of Month]))
var _monthLast = MONTH(MAX('Table'[Last Day of Month]))
var _dayLast = DAY(MAX('Table'[Last Day of Month]))
return
DATEDIFF(DATE(_year , _month , 1) , DATE(_yearLast , _monthLast , _dayLast) ,  DAY) + 1

A table visual will look like this:

Hopefully, this is what you are looking for.

Regards,
Tom