Hello, I would need to create a metric to bring me the last day of the month that I am selecting in the filter of a report.
If I select the day March-18-2021, it should bring me the number 31.
If I select the day April-14-2021, it should bring me the number 30.
And so on, 28 in February (or 29 if that year is a leap year).
Thank you very much!
Best answer by Joseph HeunView original
This can be accomplished by utilizing MIN and MAX dates through date arithmetic. An example metric in this instance would read as:
Last Day of the Month metric:
SELECT MAX (Date (date dimension)) BY Month/Year (date dimension)
If you are using this do count certain measures during a month you could use the previous metric as an internal metric for the following:
SELECT [Measure Event] WHERE Date (date dimension)=Last Day of the Month metric
Thanks for your quick response Joseph.
I have been testing with the following metric:
SELECT MAX(Month Date (Disconnect Date)) BY Month/Year (Disconnect Date).
When I select the date 06.Jan.22, I got the number 44,566.
What I need to get is only the number of the last day of the month (28, 29, 30 or 31, depending on the month I am selecting) and not the full date (31 Jan 2022).
Thanks again for your help.
I’m afraid that our date dimensions won’t necessarily work like that. If you are just looking to display the last value of the month in a singular numerical format, the best option would be to upload a new dataset with the date value in the month. You could then count the values that way and use them within your reporting.
Thank you very much for your reply. I will try to modify the dataset.