Forum Discussion
Advocate IICalculation of time spent
The time from creation to first update should work in a very similar way:
_firstResponseDays = IF( ISBLANK(updateLog[Created]), DATEDIFF(creationLog[Date Created], creationLog[Date Updated], DAY), DATEDIFF(creationLog[Date Created], MIN(updateLog[Created]), DAY) )The timings between to's and fro's in the updateLog table goes beyond this a bit.
I would probably add an index column in PQ that starts at zero to your updateLog table, then merge the table on itself on:
[Master ID] = [Master ID] and [Update ID] = [newIndexColumn]
This will allow you to get a 'from' date and 'to' date between each interaction.
Pete
Advocate IIHi,
Thanks for your reply, I did not consider doing it in DAX, maybe it would be easier.
However, it is necessary to take into account not just the time from the receiving of the final update to execution, but also the time from creation to providing the first update, and from receiving the first update to providing the second update.
Do you think there is a way to calculate this in DAX?
Super User
The time from creation to first update should work in a very similar way:
_firstResponseDays =
IF(
ISBLANK(updateLog[Created]),
DATEDIFF(creationLog[Date Created], creationLog[Date Updated], DAY),
DATEDIFF(creationLog[Date Created], MIN(updateLog[Created]), DAY)
)
The timings between to's and fro's in the updateLog table goes beyond this a bit.
I would probably add an index column in PQ that starts at zero to your updateLog table, then merge the table on itself on:
[Master ID] = [Master ID] and [Update ID] = [newIndexColumn]
This will allow you to get a 'from' date and 'to' date between each interaction.
Pete
- itskool
BA_Pete Amazing, thank you! It worked!
I modified it a bit for my case - since my IDs are not consequent, I added the indices for each Master ID.
- BA_Pete
Glad it's worked for you, great news for last thing on a Friday!
I forgot to mention: You could actually sort your updateLog table first by [Master ID], then by [Created], both ascending, then add an Index starting from 1, then another starting from zero, to ensure you have all the info you need in the correct places to do the self-join, but sounds like you've sorted it out yourself.
Have a good weekend!
Pete