Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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?
Solved! Go to 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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thank you! Is there any easy way to break down the DateDif to show Days, Hours, Mins? Some of the updates are days later.
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
101 | |
78 | |
70 | |
63 |
User | Count |
---|---|
141 | |
106 | |
101 | |
85 | |
72 |