Dashboard Display Current Month

Jessica Fontenot
edited 12/09/19 in Smartsheet Basics

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?

 

Smarsheet Sales Report.jpg

Comments

  • Paul Newcome
    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å
    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

    work-bold

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • 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
    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.