Changing your financial year in Looker Studio

Why custom financial reporting periods matter

Imagine you’ve just closed the first quarter of your fiscal year, but it doesn’t align with the traditional January to March calendar. Your fiscal year starts in August. You’re looking at your financial data, but the built-in quarters in Looker Studio aren’t making sense. This is a common scenario for many businesses operating on non-standard fiscal years.

Flexibility is the core advantage of creating custom financial reporting periods in Looker Studio. Custom quarters and fiscal years allow your reports to align perfectly with your business operations. Let’s take a closer look at how to set this up.

How does this formula work

1. Determine fiscal year

   • If the month is August (8) or later, add 1 to the calendar year.
   • If the month is before August, use the current calendar year.

2. Determine fiscal month

   • If the month is August (8) or later, subtract 7. Add a leading zero if the result is a single digit.
   • If the month is before August, add 5. If the result is a single digit, add a leading zero.

3. Combine components

   • To create a valid date string, concatenate the fiscal year, the zero-padded fiscal month, and the day ’01’.
   • PARSE_DATE is then used to convert this string into a date.

Example outputs

Original Date: 2023-09-15

Fiscal Start Date: 2024-02-01 (Fiscal Year 2024, Fiscal Month 2 – September).

Original Date: 2023-07-15

Fiscal Start Date: 2023-12-01 (Fiscal Year 2023, Fiscal Month 12 – July).

Copy and paste the formula below into a custom field

To start, copy and paste this formula and use the custom field as a date dimension.

PARSE_DATE('%Y%m%d',
  CONCAT(
    CASE 
      WHEN MONTH(Date_Field) >= 8 THEN CAST(YEAR(Date_Field) + 1 AS STRING)
      ELSE CAST(YEAR(Date_Field) AS STRING)
    END,
    CASE 
      WHEN MONTH(Date_Field) >= 8 THEN
        CASE
          WHEN MONTH(Date_Field) - 7 < 10 THEN CONCAT('0', CAST(MONTH(Date_Field) - 7 AS STRING))
          ELSE CAST(MONTH(Date_Field) - 7 AS STRING)
        END
      ELSE
        CASE
          WHEN MONTH(Date_Field) + 5 < 10 THEN CONCAT('0', CAST(MONTH(Date_Field) + 5 AS STRING))
          ELSE CAST(MONTH(Date_Field) + 5 AS STRING)
        END
    END,
    '01'  -- Assuming you want the first day of the fiscal month
  )
)

How the formula works

1. Fiscal year calculation

   • The formula checks the month of the date field.
   • If the month is August (8) or later, it adds 1 to the calendar year. This means the fiscal year alignment shifts to the next calendar year.
   • If the month is before August, the fiscal year remains the current calendar year.

2. Fiscal month calculation

   • The formula adjusts the month number to align with the fiscal year start.
   • For months from August (8) onwards, the formula subtracts 7 to make August month 1 of the fiscal year. If the result is a single-digit number (less than 10), it prepends a ‘0’ to maintain the two-digit format required for date parsing.
   • For months before August, the formula adds 5 to shift the months to the last part of the fiscal year. Similarly, it ensures the month is zero-padded to a two-digit format.

3. Combining fiscal year and month

   • The fiscal year and zero-padded fiscal month are combined with ’01’ as the day to create a valid date string.
   • The PARSE_DATE function is then used to convert this string into a date format.

PARSE_DATE('%Y%m%d',
  CONCAT(
    CASE 
      WHEN MONTH(Date_Field) >= 8 THEN CAST(YEAR(Date_Field) + 1 AS STRING)
      ELSE CAST(YEAR(Date_Field) AS STRING)
    END,
    CASE 
      WHEN MONTH(Date_Field) >= 8 THEN
        CASE
          WHEN MONTH(Date_Field) - 7 < 10 THEN CONCAT('0', CAST(MONTH(Date_Field) - 7 AS STRING))
          ELSE CAST(MONTH(Date_Field) - 7 AS STRING)
        END
      ELSE
        CASE
          WHEN MONTH(Date_Field) + 5 < 10 THEN CONCAT('0', CAST(MONTH(Date_Field) + 5 AS STRING))
          ELSE CAST(MONTH(Date_Field) + 5 AS STRING)
        END
    END,
    '01'
  )
)

Adapting the formula for different clients

You must adjust the month offset logic to adapt this formula for clients with different fiscal years and month starts. Let’s examine various scenarios to see how to do this.

Example for the fiscal year starting in January

Fiscal year calculation

If the fiscal year begins in January, the fiscal year is the same as the calendar year throughout

CAST(YEAR(Date_Field) AS STRING)

Fiscal Month Calculation

Since January is the first month, no adjustments are needed:

CASE
  WHEN MONTH(Date_Field) < 10 THEN CONCAT('0', CAST(MONTH(Date_Field) AS STRING))
  ELSE CAST(MONTH(Date_Field) AS STRING)
END

Example for fiscal year starting in April

Fiscal year calculation

If the month is April (4) or later, add 1 to the calendar year.

CASE 
  WHEN MONTH(Date_Field) >= 4 THEN CAST(YEAR(Date_Field) + 1 AS STRING)
  ELSE CAST(YEAR(Date_Field) AS STRING)
END

Fiscal month calculation

• Subtract 3 from the month number for April (to make it month 1) to December.
• Add 9 to the month number for January to March.

CASE 
  WHEN MONTH(Date_Field) >= 4 THEN
    CASE
      WHEN MONTH(Date_Field) - 3 < 10 THEN CONCAT('0', CAST(MONTH(Date_Field) - 3 AS STRING))
      ELSE CAST(MONTH(Date_Field) - 3 AS STRING)
    END
  ELSE
    CASE
      WHEN MONTH(Date_Field) + 9 < 10 THEN CONCAT('0', CAST(MONTH(Date_Field) + 9 AS STRING))
      ELSE CAST(MONTH(Date_Field) + 9 AS STRING)
    END
END

Generalisable modifications for any start month

To generalise the formula, you can introduce parameters or variables within Looker Studio or manually adjust offsets according to your fiscal start month.

For instance, let’s consider a parameterised start month:

Define the fiscal year adjustment variable

Determine how many months to adjust the year increment. For example, start_month = 4 (April), year_adjust = 3 (because 12 – 4 + 1).

CASE 
  WHEN MONTH(Date_Field) >= start_month THEN CAST(YEAR(Date_Field) + 1 AS STRING)
  ELSE CAST(YEAR(Date_Field) AS STRING)
END

Define the fiscal month adjustment

Calculate the fiscal month offset based on the start month. If start_month = 4, offset = 3. If start_month = 8, offset = 7.

CASE 
  WHEN MONTH(Date_Field) >= start_month THEN
    CASE
      WHEN MONTH(Date_Field) - offset < 10 THEN CONCAT('0', CAST(MONTH(Date_Field) - offset AS STRING))
      ELSE CAST(MONTH(Date_Field) - offset AS STRING)
    END
  ELSE
    CASE
      WHEN MONTH(Date_Field) + (12 - offset) < 10 THEN CONCAT('0', CAST(MONTH(Date_Field) + (12 - offset) AS STRING))
      ELSE CAST(MONTH(Date_Field) + (12 - offset) AS STRING)
    END
END

Example adapting for another start month (October)

Fiscal year calculation

CASE 
  WHEN MONTH(Date_Field) >= 10 THEN CAST(YEAR(Date_Field) + 1 AS STRING)
  ELSE CAST(YEAR(Date_Field) AS STRING)
END

Fiscal month calculation

CASE 
  WHEN MONTH(Date_Field) >= 10 THEN
    CASE
      WHEN MONTH(Date_Field) - 9 < 10 THEN CONCAT('0', CAST(MONTH(Date_Field) - 9 AS STRING))
      ELSE CAST(MONTH(Date_Field) - 9 AS STRING)
    END
  ELSE
    CASE
      WHEN MONTH(Date_Field) + 3 < 10 THEN CONCAT('0', CAST(MONTH(Date_Field) + 3 AS STRING))
      ELSE CAST(MONTH(Date_Field) + 3 AS STRING)
    END
END

Customising your fiscal periods in Looker Studio can transform your business’s reporting accuracy. It requires a bit of setup, but the resulting insights and alignment with your operations make it worthwhile. Now that you’re equipped with these steps tailor Looker Studio to your fiscal needs.

Got more questions? Give me a shout.

Creating custom quarters in Looker Studio now feels more like a well-brewed strategy meeting with a pal over coffee than a daunting task.

Happy reporting!