I am trying to calculate Month-over-Month (MoM) metrics dynamically in Looker Studio, but I am unsure how to implement this correctly. The query below works only for the current and previous month, and it does not adjust dynamically when I select a different date range (for example, selecting data from three months ago and comparing it with the month before that when trying to calculate the MOM%.
SUM(CASE WHEN DATE_TRUNC(o.partition_date, MONTH)= DATE_TRUNC(CURRENT_DATE(), MONTH) THEN od.price ELSE 0 END) AS current_month_value,
SUM(CASE WHEN DATE_TRUNC(o.partition_date, MONTH)= DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), MONTH)
THEN od.price ELSE 0 END) AS previous_month_value
However, this approach is not dynamic and does not respond correctly to user-selected date ranges in Looker Studio. I need a solution that dynamically compares the selected month with its immediately preceding month when trying to calculate MOM%.
my current and previous month query: