Duplicate Dashboard but with Date Filter

John Stanik

Hi,

I am looking for ideas on creating a filter for date ranges in a dashboard. I want users to view the available data in a dashboard by quarter (three months of data).

I have a dashboard built that contains all of the data for 2021 that is fed from a single sheet. I intended to continue adding rows to the same sheet for 2022.

I believe that there is NOT an option to filter by date within the dashboard itself, but is there an easy way to create secondary dashboards (by quarter) that are identical to the original but only display data for a specific date range?

I think I would have to create a new sheet that feeds from the original sheet but only includes data for my specific time frame - I'm not sure how to do that (or if it's possible).

Any suggestions would be much appreciated!

Best,

John Stanik RN, BSN

Andrée Starå

@John Stanik

Happy to help!

I recently developed three different similar versions in the Master Metric sheet of the projects.

• I used Sheet Summary to Select the Year that I wanted to show.
• Sheet Summary or section in the Metrics Sheet to select the Year, client, and more.
• Form to specify the criteria, and then the Dashboard would reload with everything up to date.

For your need, it seems that either option 1 or 2 would be the best method, but the issue is that all formulas need to be updated to reference the selector(s).

Make sense?

Would that work/help?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Andrée Starå

Hi @John Stanik

I hope you're well and safe!

It depends on what you want to show in the Dashboard.

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

I hope that helps!

Have a fantastic week & Happy New Year!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Maaik Meijerink

Hello @John Stanik,

A suggestion which I have used many times before: I used 2 column-formula's in 1 single sheet, together determining which month it is compared to the current month. This of course can be 1 column formula, but is easier to understand for now if splitted:

Column Specified Month:

=YEAR(Date@row) + "-" + RIGHT("00" + MONTH(Date@row); 2)

Column This Month Diff:

=IF(AND(Year@row <> ""; Month@row <> ""); VALUE(YEAR(TODAY()) + RIGHT("00" + MONTH(TODAY()); 2)) - VALUE(Year@row + "" + RIGHT("00" + Month@row; 2)))

Example: Say Today's month is 2022-01 and Specified Date = 05-02-22 (dd-mm-yy)

Then the results are

Specified Month: 2022-02

This Month Diff: -1

Of course you can then use a report on your This Month Dashboard Using Reports which are based on a filter: This Month Diff = 0 and on your Next Month Dashboard using a report with filter This Month Diff = -1.

Be aware: I have not takan into account transition from one year to the other: not needed As I only needed dashboards Last Month / This Month / Next Month and therefore I only needed the filters: equal to 0, greater than 0, less than 0. If anyone can help me with the year transition - formula in case I need it in the future ?

John Stanik

@Andrée Starå

Hello Andree,

Thank you so much for the quick response. I shared the sheets and the dashboard with you for review. To answer your question: I want to show all of the data that is included in the dashboard, but by the date range. When I say "date range", I'm looking for projects (rows) that were started during a specific quarter AND projects that were completed in that same quarter.

Hope that helps clarify.

Best,

John

John Stanik

@Maaik Meijerink

Hello Maaik,

Thank you so much for the quick response. I'm not quite sure I understand how this works. Is it dependent on a report generated from the main sheet and the dashboard pulling from the report? I currently have the bulk of the data pulling directly from the main sheet or from a second sheet that pulls from the main sheet (as well as a couple of reports).

Sorry if this does not make sense!

Best,

John

Andrée Starå

@John Stanik

Happy to help!

I recently developed three different similar versions in the Master Metric sheet of the projects.

• I used Sheet Summary to Select the Year that I wanted to show.
• Sheet Summary or section in the Metrics Sheet to select the Year, client, and more.
• Form to specify the criteria, and then the Dashboard would reload with everything up to date.

For your need, it seems that either option 1 or 2 would be the best method, but the issue is that all formulas need to be updated to reference the selector(s).

Make sense?

Would that work/help?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!