How do I filter on two dates?

TheBardsGirl

Long time lurker, first time poster here.

I need to create a metric within either the ETL stage or directly in the dashboard (I suspect it will need to be don at dashboard level) for meetings attended. It is calculated as:

Count of Marked as done date within date range AND Due date within data range

So for example if the dashboard is currently filtered to look at the previous month the metric will be: Count of Marked as done date between 1st Feb 22 and 28th Feb 22 AND Due date between 1st Feb 22 and 28th Feb 22

The marked as done date and due date wont always be the same day as the due date could be 28th Feb and the done date could be 1st March, in which case it wouldn't be counted in the metric.

Is there a way within the dashboard/cards to take two dates into consideration and if not is there a work around?

Also, for the record, I do not agree with how we are calculating this in the first place, but lets gloss over that.

TheBardsGirl

Thank you for your response.

That would work if the dashboard was only ever looking at previous month, but they have the ability to change the date range, to anything

Ashleigh

@TheBardsGirl If users can change the month then I don't think an ETL would work cause it would not be fully dynamic. You could try to setup a beast mode that looks at min and max dates so it takes in whatever range they choose.

MarkSnodgrass

It looks as though you are wanting to count when something is done and due in the same month. In which case, I would do this:

CASE WHEN Month(duedate) = Month(donedate) AND YEAR(duedate) = YEAR(donedate) THEN 1
ELSE 0 END

You can then sum this beast mode to get the count.

TheBardsGirl

Thank you for your response.

That would work if the dashboard was only ever looking at previous month, but they have the ability to change the date range, to anything

Ashleigh

@TheBardsGirl If users can change the month then I don't think an ETL would work cause it would not be fully dynamic. You could try to setup a beast mode that looks at min and max dates so it takes in whatever range they choose.