Forum Discussion
Recontact count using DAX
Hi, hope someone can help me with this.
I have a 2 columns table with Date and Tickets count.
I need to add a new column using DAX (can also be a measure instead) that will give me the recontact count.
Here is how the table works.
The table shows how many time a person called the company on a giving date.
The first date to appear in the table is the first base date, every new ticket within 7 days will count as recontact of the original ticket.
So if the person calls 2 times on the same day and does not call again within 7 days, we will have 2 tickets and 1 recontact.
When the tickets of a day are counted as recontacts of a previsou date, they can no loger add up to any other date, not even its original date.
Here is a table with an example, the third column is the column I need to create using DAX.
Understanding the table:
- 06/03 is the first date in the table, every ticket (but the original ticket) within 7 days will count as recontact. From 06/03 to 13/03 (6+7) we have a total of 6 tickets, meaning there are 5 recontacts.
- Days 09/03 to 13/03 have 0 recontacts, as their tickets have already been added as a recontact of day 06/03.
- Days 14/03 and 22/03 have no recontact as each had only 1 ticket and they are more than 7 days apart.
- 10/04 have 5 recontacs as it sums all tickets from the following 7 days, so from 10/04 up to 17/04. Wich totals 6 tickets, meaning 5 recontacts.
- All following days have no recontacts as they have already been added to the recontacts of 10/04.
| Date | Tickets | Recontact |
| 06/03/2023 | 1 | 5 |
| 09/03/2023 | 2 | 0 |
| 11/03/2023 | 2 | 0 |
| 13/03/2023 | 1 | 0 |
| 14/03/2023 | 1 | 0 |
| 22/03/2023 | 1 | 0 |
| 10/04/2023 | 1 | 5 |
| 12/04/2023 | 2 | 0 |
| 13/04/2023 | 1 | 0 |
| 14/04/2023 | 1 | 0 |
| 17/04/2023 | 1 | 0 |
4 Replies
Hi rodrigo_avf,
I think I've solved your problem via an additional table. If you check and confirm that the result on your working data is fine, I will be able to pack this table into a calculated column or a measure (another option - to create three additional columns but use only the final one).
AugmentedTable = ADDCOLUMNS ( ADDCOLUMNS ( ADDCOLUMNS ( data, "Draft", VAR StartDate = [Date] VAR EndDate = StartDate + 7 RETURN SUMX ( FILTER ( data, [Date] >= StartDate && [Date] <= EndDate ), [Tickets] ) - 1 ), "Flag", VAR CurrentDate = [Date] VAR PreviousDate = CurrentDate - 7 VAR ExistingPreviousDate = MINX ( FILTER ( data, [Date] < CurrentDate && [Date] >= PreviousDate ), [Date] ) RETURN IF ( MINX ( FILTER ( data, [Date] = ExistingPreviousDate ), [Draft] ) > 0, 1, 0 ) ), "Result", IF ( [Flag] = 0, [Draft], 0 ) )Best Regards,
Alexander
Hi barritown
Thank you for taking the time to try and help me.
Unfortunatelly that did not solve the problem.
Here is a screen shot of the results, notice that it seems to work up to day 10/04 but afterwards it doesn't, the flag isn't correct. On the 18/04, 26/04, 05/05 the result should not have been 0.You'll notice a CPF column, that's the social number, that's why I hid it, I also amended your code to filter by the CPF number. But even when the original table has only 1 CPF it didn't work.
Here is the amended code
AugmentedTable = ADDCOLUMNS ( ADDCOLUMNS ( ADDCOLUMNS ( 'Cálculo', "Draft", VAR StartDate = [Date] VAR EndDate = StartDate + 7 VAR CPFSelected = [CPF] RETURN SUMX ( FILTER ( 'Cálculo', 'Cálculo'[Date] >= StartDate && 'Cálculo'[Date] <= EndDate && [CPF] = CPFSelected), [Tickets] ) - 1 ), "Flag", VAR CurrentDate = 'Cálculo'[Date] VAR PreviousDate = CurrentDate - 7 VAR CurrentCPF = 'Cálculo'[CPF] VAR ExistingPreviousDate = MINX ( FILTER ( 'Cálculo', 'Cálculo'[Date] < CurrentDate && 'Cálculo'[Date] >= PreviousDate && 'Cálculo'[CPF]=CurrentCPF), 'Cálculo'[Date] ) RETURN IF ( MINX ( FILTER ( 'Cálculo', 'Cálculo'[Date] = ExistingPreviousDate ), [Draft] ) > 0, 1, 0 ) ), "Result", IF ( [Flag] = 0, [Draft], 0 ) )Hi rodrigo_avf,
I see. My approach apparently doesn't fit. I tried a couple of other ideas today, but they didn't work either. Should I solve it via some programming language, I'd get rid of the odd dates in a series of cycles first and then perform the calculation for the rest, but that's not about [DAX].
Hope someone from Community solves your problem, I'll be happy to watch and learn.
Should I get an "Evrika!" insight, I'll get back to you.
Best Regards,
Alexander
