Measure for Previous Year value

Looker Q&A Looker Studio
, ,
1

Hi there,

I am quite new to using Looker Studio.
I have always worked on Power BI in the past, however, in the new company, Looker Studio is now being introduced (coming from Qlik).
The difficulty I’m encountering is recreating a field based on the date field for PY (Previous Year):
Calculated simply as “current date - 364 days.”
Below an example of what i want to achieve.

Mau17_0-1727854329109.png
Mau17_0-1727854329109.png580×236 6.39 KB

I had to blend the data to achieve this, but by doing so, the control over the date range no longer seems to work.

I would like to achieve this just with the creation of a measure if possibile.

I already created the datePY column i need:

Date_PY= DATE(DATETIME_SUB(data_date, INTERVAL 364 day)) , but I cannot associate any value.

I tried with

Views_PY == SUM(CASE WHEN data_date =date_py THEN total_pdp_views ELSE 0 END)

Is there a way to do it?

Thank you

2

Hi @Mau17

you can have a look at https://www.googlecloudcommunity.com/gc/Technical-Tips-Tricks/Methods-for-Period-Over-Period-PoP-Analysis-in-Looker/ta-p/587538, it contains well explained guides to use PoP methods. In your example, you could have a look at method 3 Current Period and Previous Period.
It offers you the option of parametrising the period you want to compare your data with, but for your example, you can use the concept that i mention below, where you create a measure that returns ‘this’ or ‘last’ depending on your conditions, then you filter your measure using it:

## ---------------------- TO CREATE FILTERED MEASURES ---------------------------- ##

        dimension: period_filtered_measures {
        hidden: yes
        description: "We just use this for the filtered measures"
        type: string
        sql:
            {% if current_date_range._is_filtered %}
                CASE
                WHEN {% condition current_date_range %} ${created_raw} {% endcondition %} THEN 'this'
                WHEN ${created_date} between ${period_2_start} and ${period_2_end} THEN 'last' END
            {% else %} NULL {% endif %} ;;
        }

    # Filtered measures

        measure: current_period_sales {
        view_label: "_PoP"
        type: sum
        sql: ${sale_price};;
        filters: [period_filtered_measures: "this"]
        }

        measure: previous_period_sales {
        view_label: "_PoP"
        type: sum
        sql: ${sale_price};;
        filters: [period_filtered_measures: "last"]
        }

Hope it helps!