Track history data change in CRM dynamics

[Dionah]

Hi all,

I have a table called incident which contains a column named statuscode from CRM dynamics. 

This statucode is fepending on statecode  like Actif can take some values like : Starting, Examining, Studying, Closing. (for example) and Inactif : Canceled.

Now, I want to know the how long is for one incident change a statuscode?

My idea is(after searching on the web) to use the Audit table, but when I retrieve it from dataverse (using Odata from API and  Dataverse Dynamics connector, the results are the same), I don't have the old and the new values.

please could someone help me?

thanks for in advance

[Anonymous · Accepted solution]

Hi @Dionah ,

Please try the following workarounds that may help.

I created an Incident table in Dynamics CRM and then its statuscode field has the following options.

Edit the status reason transitions. Only one subsequent status reason can be selected per transition.

You can customize the stage process on demand.

Define status reason transitions in Dynamics 365 Customer Engagement (on-premises) | Microsoft Learn

Import the Incident table and Audit table into Power BI through the Dynamics CRM connector and ODATA Feed connector.

Create a disconnected table contains the status reason option.

When the status reason field is changed, the changeddata attribute in the audit table record indicates the status reason before the change.

For example, Starting (value 1) -> Studying (value 100000000), the value of the changeddata attribute is 1.

Based on this behavior, create the following measure showing the duration of time the incident stays on each status reason.

Duartion = 
VAR Starting = CALCULATE(MAX(Audit[createdon]), Audit[changedata] = BLANK(),Audit[_objectid_value]=SELECTEDVALUE(cr8e6_incident[Incident]))
VAR Studying = CALCULATE(MAX(Audit[createdon]), Audit[changedata] = "1", Audit[_objectid_value]=SELECTEDVALUE(cr8e6_incident[Incident]))
VAR Examining = CALCULATE(MAX(Audit[createdon]), Audit[changedata] = "100000000",Audit[_objectid_value]=SELECTEDVALUE(cr8e6_incident[Incident]))
VAR Closing = CALCULATE(MAX(Audit[createdon]), Audit[changedata] = "100000001",Audit[_objectid_value]=SELECTEDVALUE(cr8e6_incident[Incident]))
VAR Canceled = CALCULATE(MAX(Audit[createdon]), Audit[changedata] = "0~100000002",Audit[_objectid_value]=SELECTEDVALUE(cr8e6_incident[Incident]))
RETURN

SWITCH(SELECTEDVALUE(StatusReason[statuscode]),
    "Starting",IF(Studying<>BLANK() ,Studying - Starting , UTCNOW() - Starting),
    "Studying",IF(Studying<>BLANK(),IF(Examining<>BLANK(), Examining - Studying, UTCNOW() - Studying), BLANK()),
    "Examining", IF(Examining<>BLANK(),IF(Closing<> BLANK(), Closing - Examining, UTCNOW() - Examining),BLANK()),
    "Closing", IF(Closing<>BLANK(),IF(Canceled<>BLANK(), Canceled - Closing, UTCNOW() - Closing),BLANK()),
    "Canceled", IF(SELECTEDVALUE(cr8e6_incident[statecodename])="Inactive", UTCNOW() - Canceled, BLANK())
)

Since UTC time is used in Dynamics CRM, therefore UTCNOW() is used in the measure.

The final result is below. Hope this helps you.

Please see the attached pbix for reference.

Best Regards,
Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.