Google Data Studio / Looker Studio : scorecard comparing vs prior month based on a filter

Question

1) Data I have a following dataset in google sheets link. In the sheets sample I have only 4 months of data but normally there would be many, many more to come in the future.

MONTH	DATE	KATEGORIE	DOWNTIME	TIME (min)
9	01/09/2021	01 DURCHLAUF	0	50
9	02/09/2021	01 DURCHLAUF	0	65
9	03/09/2021	01 DURCHLAUF	0	91
9	04/09/2021	01 DURCHLAUF	0	52
9	05/09/2021	01 DURCHLAUF	0	72
9	06/09/2021	01 DURCHLAUF	0	44
9	07/09/2021	01 DURCHLAUF	0	55
9	08/09/2021	01 DURCHLAUF	0	30
9	09/09/2021	01 DURCHLAUF	0	42

2) Expected output table and desired output

I want to create a scorecard for 02 Downtime to show total time for a given month.

If I filter for November, I would like the scorecard to compare vs October. (abs=1180, %=45) Similarly, if I select December, I want to see the amount vs November (abs=940, %=25)

As a safety measure, if someone selects 2 months simultaneously, then perhaps it should not show any comparison. (unless it's possible to even do 2 vs 2 months, but it's not a necessity.)

3) Chart: Configuration + Setup

I have created a simple scorecard and a pivot table. I filtered out only Downtime.

4) Issue: Attempt at solving + Output and 5) Report: Publicly editable Looker Studio with 1-4.

In my file link you see the mentioned scorecard but I fail to include any comparison that is kind of "dynamic" that changes the month in question.

Answer 1

Added solution to your dashboard page 2

• Cross join the datasource with itself
• use the following calculated fields for expected output.

Daet01:

CAST(CONCAT(DATE(EXTRACT(YEAR FROM DATE123),EXTRACT(MONTH FROM DATE123),01)) AS DATE)

Previous Month:

TIME (min) (Table 1)-CASE WHEN Daet01 (Table 2) = DATETIME_SUB(Daet01 (Table 1), INTERVAL 1 MONTH) THEN TIME (min) (Table 2) ELSE 0 END

% Difference:

(TIME (min) (Table 1)-CASE WHEN Daet01 (Table 2) = DATETIME_SUB(Daet01 (Table 1), INTERVAL 1 MONTH) THEN TIME (min) (Table 2) ELSE 0 END)/TIME (min) (Table 2)

-