Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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.
Proud to be a Super User!
Paul on Linkedin.
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.
Proud to be a Super User!
Paul on Linkedin.
Hey @PaulDBrown ,
of course, I will try to change my habit 🙂
Never thought of this.
Regards,
Tom
Great to hear! It's pretty cool when you find that DAX has little gems hidden up its sleeve!
Proud to be a Super User!
Paul on Linkedin.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |