Transform an Audit Trail table

[Anonymous]

Hello, I have a question on how to transform an "audit" table to make it easier to utilize..

1) We have a table of "Issues" and thier current values.

2) In this example we have an audit table that shows the changes in teh value of the field "Team"

3) we want to transform that table to show the date range for the value in the Team field.

Is there an easy way to transform the Audit table... or is there a way to create a calculated column in the first "Team Changes" table that coudl calculate the duration for each value in the TEAM field per BUG... ?

this data is coming from our JIRA system via a REST api call to get the "changelog" for each Jira Issue.  So we would have to do any tranformations in either power query or as calculated columns/tables in DAX.. 

[Ray_Brosius]

@Icey 

I changed jobs to a new company and I am trying to re-create the same scenario in this topic.  I can't find the solution to this post?  Did we go off line?  ANy help refreshing my memory on this challenge...

Thanks in advance.. 

[Icey]

Hi @Anonymous ,

---

3) we want to transform that table to show the date range for the value in the Team field.

---

Where does "1/5/2020" come from? What is the calculation logic of the "Date From" and "Date To" columns?

Best Regards,

Icey

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

[Anonymous]

@Icey 

I was not clear enough... Sorry About that..

The first table shows the "CUrrent" data for the bugs.  we use this to get the Date the Bug was created.

The second table is the format we get for the audit trail.  It shows the bugID, the Field being changed(TEAM), the Date the change occured and then the previous TEAM value and the new TEAM value ( From, To). 

We want to transform that into the third table which shows the date range for the TEAM value on each bug.  I need some help on how best to do that.

For this new table, we would calculate the From Date and TO dates which would show the date range for the value of the field . 

From the original audit table if the FROM field value was blank, The rule would use the date the bug was created from the Current Data table, as the Date From. 

For The Date To column we need to get the NEXT value in the chain for the BUG and the date the change occured.  This would become the Date To value.

[Anonymous]

Here is another way to "explain" / look at this..

See the example table below...  We need to calculate the END Date for each audit record.  The "END Date" woudl be the ChangeDate for the NEXT record in the audit table.  Key being that we need to get the Next Record for the same Bug..

So on row 2 the item BUG-15157 was assigned to Bill Smith from 5/6/2020 until 5/10/2020( the date of the next change in Assignee for the BUG.

My challenge is how to get the "NEXT" record and the corresponding ChangeDate for EACH BUG.  Also how to handle the LAST record in the change and return "BLANK"

Assignee Audit Table