Year On Year Comparison In Google Data Studio By Fiscal Year

Year on Year comparison is very essential for investors to gauge company’s financial performance through year and compare it with the previous year.

Tons of data is being generated at every moment through different machines, internet enabled devices and other sources. The data must be gathered, processed and analyzed for better understanding of relationships and patterns in business. This is where data visualization steps in.

Data Visualization interactive tools like Google Data Studio and many others greatly benefits the organizations to quickly interpret the data in real time and draw the meaningful insights.

Most of the businesses use fiscal year rather than calendar year for YOY comparisons. However, fiscal year is not available as custom time dimension in Google Data Studio. That’s where the custom queries steps in.

This document would be helpful for YOY comparison in Google Data Studio by fiscal year.

To find YOY comparison for fiscal calendar in Google data studio using custom queries and calculated fields.

Where we can use:

  • To find the “This year” vs “Last year” comparisons for various KPIs for retail year.
  • This feature is essential for decision makers in any organization.

How to Implement?

Consider the below example where we want to find the This Year (TY) sales comparison with Last Year (LY) for fiscal year. ( Note : Please refer the fiscal calendar 454 for more details )

Step 1: Create a view in BigQuery to find TY & LY weekly comparison

This will give the TY & LY data at daily level:

Step 2: Create view in BQ to Join the Table1 created in 1st step with dim_date_454 to get customised time dimension in data

studio:

So that we can add the custom time dimension as fiscal week, fiscal month, fiscal quarter, fiscal year to data studio:


Step 3: Add the Table2 created in step 2 to data studio

 

alt text

Step 4: Add the scorecard to report and select metric as below:

alt text


alt text

Step 5: Calculate “COMP” as below:

  1. Select “create new field” at bottom right corner

alt text

  1. It will be directed to the page below:

      Add formula for vists_comp as : (visits_ty - visits_ly) / visits_ly

      alt text

      1. Go to All Fields

      alt text

      1. Change type for visits_comp from “number” to ‘percent’ and click on Done

      alt text

      1. Add scorecard for COMP as below:

      alt text

      1. Click on view report

      alt text

      1. Select required fiscal week from the drop down

      alt text

      1. Get the final report for TY, LY and COMP

      alt text

       

      Leave a comment