Forum Discussion

BrianaHop's avatar
BrianaHop
Icon for Helper I rankHelper 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 Creati...
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

Fowmy's avatar
Fowmy
Icon for Super User rankSuper 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
Icon for Helper I rankHelper 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
    Icon for Super User rankSuper 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