Forum Discussion
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...
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 🙂
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 🙂
