Dashboard Display of Current Month's Value

Barb Littrell

I'm trying to figure out a work-around for an non-existing feature in Smartsheet. My goal is to display the running total (only) of a chart by using a metrics widget.

The picture shows what I would like to see.

My struggle is to figure out the correct formula I can use inside my data sheet or sheet summary. Here is the data sheet:

On the dashboard I want to show a comparison of the cumulative values of each current month.

While this is easy for the actuals (I just display the YTD value of the "Actual" or "2021" rows), but I don't know what formula to use to only pick out and display the current months' values for "Budget", "Forecast" and "2020".

Example: Since we're in July, I want to show July's 2021 Purchase Order value next to July's 2020 Purchase Order value. Next month the display should only reference August values.

Any help is truly appreciated!

Mike Wilday

You would have to use an IF formula, and incorporate the Month and today formulas too... You would have to build out the metrics to display each Actual, Budget, Forecast... And replace X with the correct row number.

=IF(Month(Today())=1, JanuaryX, IF(Month(Today())=2, FebruaryX, IF(Month(Today())=3, MarchX, IF(Month(Today())=4, AprilX, IF(Month(Today())=5, MayX, IF(Month(Today())=6, JuneX, IF(Month(Today())=7, JulyX, IF(Month(Today())=8, AugustX, IF(Month(Today())=9, SeptemberX, IF(Month(Today())=10, OctoberX, IF(Month(Today())=11, NovemberX, IF(Month(Today())=12, DecemberX))))))))))))

You will need this for each metric you're tracking but it should pull the correct row from the correct column based on the month of the current date.

Mike Wilday

You would have to use an IF formula, and incorporate the Month and today formulas too... You would have to build out the metrics to display each Actual, Budget, Forecast... And replace X with the correct row number.

=IF(Month(Today())=1, JanuaryX, IF(Month(Today())=2, FebruaryX, IF(Month(Today())=3, MarchX, IF(Month(Today())=4, AprilX, IF(Month(Today())=5, MayX, IF(Month(Today())=6, JuneX, IF(Month(Today())=7, JulyX, IF(Month(Today())=8, AugustX, IF(Month(Today())=9, SeptemberX, IF(Month(Today())=10, OctoberX, IF(Month(Today())=11, NovemberX, IF(Month(Today())=12, DecemberX))))))))))))

You will need this for each metric you're tracking but it should pull the correct row from the correct column based on the month of the current date.

Barb Littrell

Thanks @Mike Wilday for the response and solving my issue.

This worked like a charm! Happy Friday :)

Mike Wilday

You're welcome! Do you think you could accept my answer? :)

Barb Littrell

Hey Mike, I already did last week, unless you're asking for something different? Please advise if so.

Mike Wilday

Whoops I think I must have been dreaming. I thought you accidentally selected your response as the best answer! :D Oh well! Have a great week!