Hey everyone!
I've been given a challenge and so far I can't wrap my head around it, even though it looks so simple in theory.
I have a table with a series of replies back and forth between the dealer and an auditor. Each row has 3 columns:
ID_Action - ID where are replies belong to, integer
Comments_Date - Date of the comment, date
Comments_Owner - Who replied, string (audit or dealer)
My goal is to calculate the average number of days it took for the audit to reply to the dealer (days between last dealer comment and first audit comment), per id_action.
Example of the table is:
| ID_Action | Comments_Date | Comments_Owner |
| 1 | 01-01-2016 | Dealer |
| 1 | 05-01-2016 | Audit |
| 1 | 06-01-2016 | Audit |
| 1 | 14-02-2016 | Dealer |
| 1 | 14-02-2016 | Dealer |
| 1 | 28-02-2016 | Audit |
| 2 | 07-07-2018 | Audit |
| 2 | 18-07-2018 | Audit |
| 2 | 25-07-2018 | Dealer |
And the expected shoud be something like this:
| ID_Action | Comments_Date | Comments_Owner | Days between replies |
| 1 | 01-01-2016 | Dealer | 0 |
| 1 | 05-01-2016 | Audit | 4 |
| 1 | 06-01-2016 | Audit | 0 |
| 1 | 14-02-2016 | Dealer | 0 |
| 1 | 14-02-2016 | Dealer | 0 |
| 1 | 28-02-2016 | Audit | 14 |
| 2 | 07-07-2018 | Audit | 0 |
| 2 | 18-07-2018 | Dealer | 0 |
| 2 | 25-07-2018 | Audit | 7 |
| 2 | 27-07-2018 | Dealer | 0 |
I've searched around here and here but none of these apply to my scenario.
So far, I am yet to calculate the last reply date for the audit replies, considering thats necessary in order to calculate the difference in days. This is what i got in a calculated column:
Last Audit Reply =
MAXX(
FILTER(
'Comentários - Histórico',
'Comentários - Histórico'[ID_ACTION] = EARLIER('Comentários - Histórico'[ID_ACTION])
&& 'Comentários - Histórico'[COMMENTS_OWNER] = "AUDIT"
),
'Comentários - Histórico'[COMMENTS_DATE]
)
But as you probably noticed, this returns the same value for each row of the same id_action (28-02-2016 for id_action 1 and 25-07-2018 for id_action 2), as I can't force the date to be lower than the next Dealer reply.
Any help would be much appreciated!
