Calculation between two measures with different date filter

Hey Florian! Neither are perfect but I’ve done this two ways:

1. Use table calculations referencing pivot/standard offsets - downside being you’ll have to create it on each Look


2. If you have standard things you want the difference of (this period/last period, etc), you could make measures with the appropriate filters (like “sales_this_period”) and make a measure for the difference (sales_this_period - sales_last_period). Then, your users just need to define the cohorts they’re comparing through parameters or filters (this is the approach I prefer - the less noodling business users have to do, the better)

Hope this helps! I’ll be back at my office in a week and can elaborate more if you still need more help 🙂

Hi @Florian_Tourneur,

Do I understand by ‘filtered independently’ that you want to calculate the difference between sfdc_prior_opportunity.contract_end_date_date and sfdc_revenue.close_date where AT LEAST ONE of the two is in the past 7 days, but not necessarily both?

In that case, I would say you should delete both the filters as shown in your screenshot, hit the Custom Filter checkbox and add:

# prior_opportunity.contract_end_date_date in the past 7 days

# OR revenue.close_date in the past 7 days

${sfdc_prior_opportunity.contract_end_date_raw} > trunc_days(add_days(-7,now()))

AND ${sfdc_prior_opportunity.contract_end_date_raw} <= trunc_days(now())

 OR

${sfdc_revenue.close_raw} > trunc_days(add_days(-7,now()))

AND ${sfdc_revenue.close_raw} <= trunc_days(now())

With the table calculation (not sure which of yours would be the start and end date):

diff_days(${sfdc_revenue.close_date}, ${sfdc_prior_opportunity.contract_end_date_date})

Hi Florian,

Glad you were able to leverage the LookML-based filtered measures for your use case.

For even more flexibility and giving your users more power, I really recommend using custom fields, and I find that sometimes it is hard to discover that they exist, so I wanted to point you to them here: https://docs.looker.com/admin-options/tutorials/managing-bizusers/bizuser-features#enabling_custom_fields