Hi all, I was wondering if there is a way to create a metric to calculate the amount of days selected in the date filter? I need to use that value to divide it with another metric in an insight

Hello Goran, getting the value from the date filter dinamically is not possible at the moment, but you could create a metric with DATETIME_DIFF to calculate the difference between any given period. Once you build that metric, you could create a new metric where it would be divided by the other metric you have, although note that the value will be hardcoded.

Hi Moises, thanks for the reply. I need to get information how many days in a given period something happened divided by the total number of days in a given period selected from the date filter. I can use DATETIME_DIFF but yes, it will be a hardcoded date. Anyways, thanks for the reply I will try to find another way to calculate it

You can use count metric to count number of unique days in a dataset considering it contains record for each day of selected period:

select count({label/date.day},{dataset/order_lines})

Other approach can be to use MIN/MAX in combination with DATETIME_DIFF if there exists record with date at the beginning and end of selected date range:

select datetime_diff(min({label/date.day},{dataset/order_lines}),max({label/date.day},{dataset/order_lines}))+1

In case the fact table contains sparse data you may want to add date dimension dataset to your model with one record for each day which would be used for counting of days. Such dataset can be in many databases created as SQL dataset. For example following query generates records for last 365 days in Snowflake:

select
  dateadd(
    day,
    '-' || row_number() over (order by null),
    dateadd(day, '+1', current_date())
  ) as date
from table (generator(rowcount => 365))

The query depends on SQL dialect of your database. You may use GENERATE_SERIES function for MS SQL or PostgreSQL for example.