Display current month's data on dashboard

Heather Hall

I have developed a metric sheet and would like only the current month's data to show on a dashboard chart without having to manually select it each month. I have seen several posts about this but can't make it work for me. Below is my sheet info. How would I get March to display currently?

       Col 1   Col 2   Col 3   Col 4   Col 5   Col 6   Col 7   Col 8   Col 9

January                      1         1                     2                                  3         

Feb                  43       65       23       28        7          3          40        4

March             1         5         29       34        3          5          7         4

April                                                                                                   

May                                                                                                    

June                                                                                                   

July                                                                                                     

August                                                                                               

September                                                                                                     

October                                                                                                          

November                                                                                                      

December      

Thanks!                                              

Paul Newcome

You would need another row (I suggest making it a parent of all of the base metrics), and then you would use a formula in each column such as

=INDEX(CHILDREN(), MONTH(TODAY()))

Basically we use the INDEX function to pull from the child rows and then we use the current month to specify which row from the children rows we pull from. Drag this across the rest of the columns in the parent row, and you should have your current month metrics which can now be referenced on your dashboard.

Paul Newcome

You would need another row (I suggest making it a parent of all of the base metrics), and then you would use a formula in each column such as

=INDEX(CHILDREN(), MONTH(TODAY()))

Basically we use the INDEX function to pull from the child rows and then we use the current month to specify which row from the children rows we pull from. Drag this across the rest of the columns in the parent row, and you should have your current month metrics which can now be referenced on your dashboard.

Heather Hall

Worked like a charm! Thanks so much Paul! :)

Paul Newcome

Happy to help. 👍️

Khasim

Hi @Paul Newcome , Could you please provide me with an example of the above question? rather than the overall formula

Paul Newcome

@khasimbashashaik I am not sure I understand what you are asking for.

SLKennedy

Hi! I have a similar ask. I have a monthly metrics sheet, but the months are the columns instead of the rows. I'd like a dashboard item to present only the current month's metrics. Any ideas?

Paul Newcome

@SLKennedy You would need a helper column with a basic INDEX function to pull the current month's data into the same column month over month then use this helper column in your charts.

=INDEX(Jan@row:Dec@row, MONTH(TODAY()))