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

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

    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.


    =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


    =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


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


    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.

  • Hi @jmyzk_cloudsmart_jp

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

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭

    Happy to help!