Forum Discussion

BrianaHop's avatar
BrianaHop
Helper I
6 years ago
Solved

Timestamping/Creating Copy of Previous Data

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?  

Need Help
  • Fowmy's avatar
    Fowmy
    6 years ago

    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

5 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    6 years ago

    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 IDLead Creation DateLead StatusLead Status Date
    101-01-20STATUS 102-01-20
    101-01-20STATUS 203-01-20
    101-01-20STATUS 304-01-20
    101-01-20STATUS 405-01-20
    101-01-20STATUS 506-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's avatar
      BrianaHop
      Helper I
      6 years ago

      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. 

  • Fowmy's avatar
    Fowmy
    Super User
    6 years ago

    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's avatar
      BrianaHop
      Helper I
      6 years ago

      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's avatar
        Fowmy
        Super User
        6 years ago

        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