Cannot compare Gsheets data to previous year

[tliebig]

I've got a GSheets Table tracking Orders and Revenue per Month like this:

The first column is a Date Column formatted as MMM YYYY).

When I pull the data into Looker Studio, I can set the timeframe of the report - e.g. for 2024-01-01 till 2024-06-30 - and Looker Studio correctly only shows the 2024 values in the table.

When I then try compare the data to the previous year (comparison timeframe), it does not find the delta and reports them as "-".

The table is set to:

• Timeframe dimension: first column of the table
• Data Dimension (granularity): YYYY MM
• compare to: previous year

How come it's not pulling the data from previous year's month correctly?

[pabboat]

Could you post the settings of the actual settings of the report? It would help me give you a more exact answer.

I would double check that you are constricting the time frame of the actual table and setting the comparison time frame correctly, the data looks fine. (Although because of how google sheets works if you have multiple types of dates in there it may error out. It looks the same in the picture but it could just be that you have a data format on it that makes it look the same but the underlying data may be different.)

Sincerely,

Dylan

[tliebig]

Alright, this is the actual table. I've set the focus to A2 to show what the field actually contains (although the formatting is set to MMM YYYY):

This is what the data table in Looker Studio is set to, with the comparison set to "previous year":

The time dimension "Datum" is the value from Column A. The dimension field "Monat" (month) is calculated from the date value in Column A with this formula:

PARSE_DATE("%m",FORMAT_DATETIME("%m",Datum))

[dylantrose]

Hey Tliebig,

While I am not entirely sure where the issue is, I will mention that I would go about accomplishing this task differently.

For "Datum", I would parse that to be a normal date, IE the original date of 01-01-2024, 02-01-2024, etc. This will ensure it works fundamentally with the date picker. I would then click the little calendar button on the dimension "Datum" and change the data type to the year month data type.

This should ensure that it works with the Comparison date range.

Here's a link to more date information.

Note: I would ensure that the "Date Range Dimension" is left as the "Date" data type, when you change it to Year Month it can get a lil' funky.

Hope this helps

Dylan

[tliebig]

Hi Dylan,

thanks for taking the time. I've tried that beforehand, and either I'm too dumb or Looker Studio is acting up (I suspect it's the former, though, but 🤷‍♂️).

Would it be too much to ask if you could set this up in an exemplary report?

This is the data: https://docs.google.com/spreadsheets/d/1tc60UncDWHmgCUBeR1oylDpytm7JKftrM1oGakgCfeQ/edit?usp=sharing and

and this is the report with the settings which I cannot for the **** of it get to calculate the change from previous year: https://lookerstudio.google.com/reporting/f5750937-a627-4f60-a111-58ab8aef619b

Go ahead an set the correct settings right there, this is just a test report which I've purposely set to publicy editable and suspect it's just gonna take you some 30seconds or so.

[dylantrose · Accepted solution]

Hey Tliebig,

It did not take me 30 seconds, I totally have not made a comparison on a table in a long time.

The issue is you are trying to compare MM YYYY to the last period. In this case, there was no last period. IE. January 2024 did not happen last year. However, January did happen last year. It happened in January of 2023.

This tool was meant to be used with text dimensions, for example if your sold 30 apples, the text dimension would be apples, so it would compare that 30 to the 30 apples sold in 2023.

I used the month operator to change your date to a month, and set the date dimension as the actual date, and it was able to do what you want. I also added a line chart, as line charts are date based and naturally show differences without the need for text dimensions.

Sincerely,

Dylan

[tliebig]

Woot, that did the trick. Thanks alot. Especially for the clarification that looker studio stumpled upon the dimension "MMM 2024", which is not present in 2023, and I have to set it to "MMM" so looker studio can look for "MMM" in 2023 then.

Thank's alot! 🙏🏻👍🏻

[tliebig]

Hi Dylan,

did you by chance have any opportunity to look into this?