Google Data Studio calculated field – Remaining number of days until the end of the month

The Google Data Studio calculated field below returns the remaining number of days until the end of the month of a specific date.


Remaining days until the end of the month
DATE_DIFF(
   DATETIME_SUB(
      DATETIME_ADD(
        DATE(YEAR(Date),MONTH(Date),1)
      , INTERVAL 1 MONTH)
   ,INTERVAL 1 DAY)
, Date)

  • Explanations of the formula step by step
    1. Create a date corresponding the the first day of the month of our date dimension (line 4)
    2. Add one month (line 3 to 5)
    3. Remove one day to go back to our current month and get the last day dynamically and taking care of leap year (line 2 to 6)
    4. Calculate the difference between the result of the 3e step and your date dimension