Scorecard of current and month and last month

[AshEff]

So I need help having my scorecard show date range control data, lets say Jan 2025 and then another that has Dec 2024 and another that has Nov 2024 for my "total leads" data. I need the data of all three to change once I change the date range, currently I have the date set so that Dec and Nov scorecards are fixed meaning I used default date range > custom > advanced so its fixed to todays date which I don't want

I believe I can create a calculated field for this after I blend the data but nothing I try works. I'd appreciate any help oh and please be thorough with directions as I'm just learning how to use Looker. Thank you

[Nandha100]

Hi @AshEff , 

Yes, you have to use some calculated fields to use the date range control like below,

example: date range i am select 2024-01-01 and 2024-01-30. That means in looker date range control has parameter called @start_date is 2024-01-01 and @end_date is 2024-01-30.

then use the start date and end date parameter for some case statement in the custom query window in looker to achieve your result.

[Looker_Studio_B]

Make a blended data

Table 1. Dimension = Date. Metric = Total Leads. Date Range Dimension. Date

Table 2. Dimension = new field CAST(DATETIME_ADD(date, interval 1 MONTH) AS Date. Metric = Total Lead = Last months Leads. Date Range Dimension. Don't use any

Table 3. Dimension = new field CAST(DATETIME_ADD(date, interval 2 MONTH) AS Date. Metric = Total Lead = L2mo Leads. Date Range Dimension. Don't use any

Left join on date = new_date = new_date, and you should be all set