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
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
Step 4: Add the scorecard to report and select metric as below:
Step 5: Calculate “COMP” as below:
- Select “create new field” at bottom right corner
It will be directed to the page below:
- Go to All Fields
- Change type for visits_comp from “number” to ‘percent’ and click on Done
- Add scorecard for COMP as below:
- Click on view report
- Select required fiscal week from the drop down
- Get the final report for TY, LY and COMP