How can I retroactively collect Metric Definition data for Demand lifecycle states?

[Alex Ionescu]

Hi everyone,

I’m working on a reporting story where I need to measure how long a Demand stays in each phase of its lifecycle. The states I’m tracking are:

• Draft

• Submitted

• Screening

• Qualified

• Approved

I’ve already created a Metric Definition for this (Field Value Duration on the State field of the Demand table) and it works fine for new records.

However, my KPI dashboard needs to show mean time per state going back to January 2024, and I noticed that Metric Definitions don’t collect data retroactively out-of-the-box.

[Mark Manders · Accepted solution]

You can't, because that data isn't there anymore. The definition calculates based on the field changing value. You don't have those timestamps from the past. Working with metrics is the same as working with PA: you data will be collected from the moment you create them. 
You could run a fix script to calculate the entire duration, based on timestamps that are on the record (like 'created on' and 'closed at'. By creating the metric records through a script, you will get some historical data, but you need the timestamps to do that. So unless you have the timestamps already stored on the record in custom fields, you won't get that historical data.