How do I setup a dynamic quarterly portfolio metric and tie it up to a widget?

Hi Community,

Good day.

I am currently preparing our portfolio dashboard. I need to setup a widget that counts project end schedules by quarter. The information I need to show is 1 quarter before current quarter, current quarter, and 3 quarters ahead.

Thank you.

Regards,

Synon

Answers

  • AdamApexConsultants
    AdamApexConsultants ✭✭✭✭
    edited 01/23/25

    The most straight forward way to accomplish this is to use helper columns on your project plans to extract the month from the end date and then assign that month number to a quarter i.e 2/5/25 would be month 2 and Q2

    From there, then you can create metrics and TODAY function to count 1 quarter before current, current quarter, 3 quarters ahead.

    Example:

    [column name] End Date : Manual input

    [column name] Helper Column - Month : =MONTH([End Date]@row)

    [column name] Helper Column - Quarter: =IF(MONTH([End Date]@row) <= 3, 1, IF(MONTH([End Date]@row) <= 6, 2, IF(MONTH([End Date]@row) <= 9, 3, 4)))

    Count Projects Metrics: 

    One Quarter Before the Current Quarter - =IF([Helper Column - Quarter]@row = IF(MONTH(TODAY()) <= 3, 4, ROUNDUP((MONTH(TODAY()) - 3) / 3, 0)), 1, 0)

    In the Current Quarter - =IF([Helper Column - Quarter]@row = ROUNDUP(MONTH(TODAY()) / 3, 0), 1, 0)

    Three Quarters Ahead - =IF([Helper Column - Quarter]@row = IF(MONTH(TODAY()) + 9 > 12, (ROUNDUP((MONTH(TODAY()) + 9 - 12) / 3, 0)), ROUNDUP((MONTH(TODAY()) + 9) / 3, 0)), 1, 0)

    These metrics would ideally be built on a metric sheet that is using cross sheet reference formulas pointed at a summary roll up sheet for all of your projects

  • Synon
    Synon ✭✭

    Hi @AdamApexConsultants,

    I get the idea of a metric sheet. How do I setup the metric sheet to denote the year and quarter? The primary column would need to be dynamic as well.

    Thank you.

    Regards,

    Synon

  • Hi @Synon -

    Do you have a portfolio roll up sheet where all your concurrent projects are being reported on?

    Or are you using reports to pull multiple projects together into a single view for your Dashboard?

  • Synon
    Synon ✭✭

    Hi @AdamApexConsultants,

    I have a portfolio level with portfolio metric sheet that pulls from intake and project level.

    I tried to use a report from the intake sheet using the aggregates, but it doesn't look proper and I can't pull the data into a chart.

    Thank you.

    Regards,

    Synon

  • AdamApexConsultants
    AdamApexConsultants ✭✭✭✭
    edited 01/23/25

    @Synon -

    Are you running multiple projects? Are you using Control Center?

    Assuming you are using control center since you have a portfolio summary and portfolio metric sheet.

    You will need to do a bit of rework to your live projects and add new metadata fields, which will then add that data to your summary roll up sheet (via a global update to update reporting)

    The metadata fields will be for Helper Column - Month AND Helper Column - Quarter

    Once those fields are added to your project metadata and portfolio summary and cell linked, then in your metrics sheet you will perform your calculations - using cross sheet reference from your metric sheet into your portfolio summary sheet.

    Count Projects Metrics: 

    One Quarter Before the Current Quarter - =IF({Helper Column - Quarter} = IF(MONTH(TODAY()) <= 3, 4, ROUNDUP((MONTH(TODAY()) - 3) / 3, 0)), 1, 0)

    In the Current Quarter - =IF({Helper Column - Quarter} = ROUNDUP(MONTH(TODAY()) / 3, 0), 1, 0)

    Three Quarters Ahead - =IF({Helper Column - Quarter} = IF(MONTH(TODAY()) + 9 > 12, (ROUNDUP((MONTH(TODAY()) + 9 - 12) / 3, 0)), ROUNDUP((MONTH(TODAY()) + 9) / 3, 0)), 1, 0)

    Once all of that is built, then you can run charts to count by one quarter, in current, three ahead

  • Synon
    Synon ✭✭

    Hi @AdamApexConsultants,

    I don't have Control Center. But I'll try to test this logic to what I'm trying to do.

    Thank you.

    Regards,

    Synon