Solved

Last day of the selected month

  • 7 January 2022
  • 4 replies
  • 147 views

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.
For example, 
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!

icon

Best answer by Joseph Heun 7 January 2022, 20:46

View original

4 replies

Userlevel 3

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.

Userlevel 3

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. 

Reply