Dashboard Display Current Month

Jessica Fontenot

Hi!

I've created my first dashboard to report sales data from a sheet that captures 1 year of data. My data rolls up the totals for each week, month and year, currently I have the widgets referencing the totals on the January row. Is there a way to display on the Dashboard the current months totals only and not for the entire year without have to edit monthly where the data comes from each month?

 

Paul Newcome

Yes. You could either use a formula on the same sheet or a different sheet to pull the current month's data. You would then use that in your Metrics Widget.

 

Does that sound like an option? If so, would you need help setting that up?

Andrée Starå

Hi Jessica, 

To add to Paul's excellent advice.

Depending on your structure and need, you could also use the new Sheet Summary and Sheet Summary Report.

More info: https://help.smartsheet.com/learning-track/smartsheet-intermediate/sheet-summary

Would that work?

I hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Jessica Fontenot

Paul,

I have not been able to figure this out with your suggestion or the other posted about Summaries. I'm assuming i need to use some type of Date Function formula, but I have no clue what to use to only return the current month's data, or how to get it to automatically change to the next current month. Any help to point me in the right direction would be greatly appreciated!!

Thanks!

 

Paul Newcome

Add in an extra helper column (these can later be hidden) for each field you want to pull data for. Then add one more.

 

In row 1 of the extra helper, enter a formula similar to this:

 

=IF(MONTH(TODAY()) = 1, "January", IF(MONTH(TODAY()) = 2, "February", IF(MONTH(TODAY()) = 3, "March", ......................................................))))))))))))

 

You will repeat the nested IF pattern until you have each month covered.

.

Top row of the first helper data column, you would enter:

 

=INDEX(CHILDREN([Other $ (hot shot fee, etc)]@row), MATCH($[Helper Month]@row, CHILDREN($CUSTOMER@row), 0))

.

You would then dragfill this across (be sure the $'s are in place before dragfilling).

 

This will essentially pull the data across row 1 in the same order as what is in your screenshot in the row labeled "2019".

.

You can then select all of these helper columns and hide them.

 

From there you can use a Metrics Widget on a dashboard to display the data.