Timestamping/Creating Copy of Previous Data

[BrianaHop]

I am pulling CRM data from MySql for a Power BI report and the goal is to have a "Speed to Contact" style report on our lead contacts. The data in Sql only stores 4 columns: 

• Unique ID
• Lead Creation Date
• Lead Status
• Lead Status Date

So for example, if a lead is created in our CRM I will see the date it was created and then the date the status was changed and I use those two fields to calculate how long it took for a sales rep to change the status on that lead (ie contact them). The problem is that I want to look past just the first change and track how long it took to change the status the first time, second time, third time, etc. Is there any way to "timestamp" or track the data from a previous refresh and use it in calculations?  

[Fowmy]

@BrianaHop 

Add this as a new column: You can change the hour to minute or  any other interval you need.

You can download the file: HERE

Time Difference = 

VAR _CURRENT = CONTRACTS[Status Changed]

VAR _PREVIOUS = 

CALCULATE(
MAX(CONTRACTS[Status Changed]),
    CONTRACTS[Status Changed] < _CURRENT,
    ALLEXCEPT(CONTRACTS,CONTRACTS[Contract ID])
)

RETURN

DATEDIFF(_PREVIOUS,_CURRENT,HOUR)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[BrianaHop]

@Fowmy 

Thank you! Is there any easy way to break down the DateDif to show Days, Hours, Mins? Some of the updates are days later. 

[Fowmy · Accepted solution]

@BrianaHop 

I have added that as well:

Time Difference = 

VAR _CURRENT = CONTRACTS[Status Changed]

VAR _PREVIOUS = 

CALCULATE(
MAX(CONTRACTS[Status Changed]),
    CONTRACTS[Status Changed] < _CURRENT,
    ALLEXCEPT(CONTRACTS,CONTRACTS[Contract ID])
)

VAR _Time = DATEDIFF(_PREVIOUS,_CURRENT,SECOND)
var vSeconds=_Time
var vMinutes=int( vSeconds/60)
var vRemainingSeconds=MOD(vSeconds, 60)
var vHours=INT(vMinutes/60)
var vRemainingMinutes=MOD(vMinutes,60)
var vDays=INT(vHours/24)
var vRemainingHours=MOD(vHours,24)
return
  IF( ISBLANK(_Time),BLANK(),
    vDays&":"&
    vRemainingHours&":"&
    vRemainingMinutes
)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[Fowmy]

@BrianaHop 

So, you need the 1st change between creation date and the Status date and the subsequent time difference will be between current row and the previous row on the Status Date? Is that what you are trying to calculate?

Unique ID	Lead Creation Date	Lead Status	Lead Status Date
1	01-01-20	STATUS 1	02-01-20
1	01-01-20	STATUS 2	03-01-20
1	01-01-20	STATUS 3	04-01-20
1	01-01-20	STATUS 4	05-01-20
1	01-01-20	STATUS 5	06-01-20

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[BrianaHop]

@Fowmy 

Correct. So this is an example of what i have coming in: 

I'm a bit of a Power BI newb so I appreciate the help.