Assistance Needed with Updating Month-Wise Data and Dashboard in Project Management

Pragati Trivedi

Hello everyone! I've recently taken over a project that was previously managed by someone else. I've observed an issue in the project's dashboard where the month-wise data, used for generating graphs, is only available until May (please refer to the attached image). Upon inquiry, I learned that the sheet containing the month-wise data was created for a single fiscal year. To continue fetching new data, I need to either create a new sheet or delete the existing one. Could someone please guide me on how to accomplish this without affecting the source sheet? I would greatly appreciate any assistance..

jmyzk_cloudsmart_jp

Hi @Pragati Trivedi

Since the data is through May, I assumed the Fiscal Year begins in June, like Microsoft.

Please open and view the dashboard in the image below at the following URL.

https://app.smartsheet.com/b/publish?EQBCT=f7aec9d85c3b4717a71342c0c307e75b

The structure of the Source sheet is like a sheet called Data. (I created the data values to get bigger incrementally).

Suppose the predecessor had somehow obtained the monthly numbers from the source data, as shown in the red background of the Monthly Trend Analysis sheet, until May 2023.

In this situation, to create Monthly Trend Analyis graphs that automatically compare the same month of the previous year to the most recent month without any changes to the source sheet, you could enter the following Columns and Columns formula in the Monthly Trend Analyis sheet, and the formula expression as below will retrieve the corresponding data from the source data.

Last-1

=IFERROR(INDEX({Data}, INDEX(COLLECT({RowID}, {Year}, YEAR(TODAY()) - 2 + IF([Month #]@row - [FY Start Month]# < 0, 1, 0), {Month}, Month@row), 1)), 0 )

{Data}, {Year} {Month} are Data sheet's column range

Last

=IFERROR(INDEX({Data}, INDEX(COLLECT({RowID}, {Year}, YEAR(TODAY())) - 1 + IF([Month #]@row - [FY Start Month]# < 0, 1, 0), {Month}, Month@row), 1)), 0 )

[FY Start Month]# = 6 in Sheet Summary

Current

=IFERROR(INDEX({Data}, INDEX(COLLECT({RowID}, {Year}, YEAR(TODAY()) + IF([Month #]@row - [FY Start Month]# < 0, 1, 0), {Month}, Month@row), 1)), 0))

Month#.

After adding the above columns, create Monthly Trend Analysis graphs for Last-1, Last and Last, Current for the same month of the previous year, starting in June.

The chart will automatically update in the future new FY.

Pragati Trivedi

Hi @jmyzk_cloudsmart_jp

This was extremely detailed and very helpful. Thank you for the insights

jmyzk_cloudsmart_jp

Happy to help!