Recontact count using DAX

[rodrigo_avf]

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

[barritown]

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

My YouTube vlog in English

My YouTube vlog in Russian

[rodrigo_avf]

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 ) )

[barritown]

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

My YouTube vlog in English

My YouTube vlog in Russian

[rodrigo_avf]

Thanks for trying @barritown 
The workaround I did was to get the range of dates each date falls in using Excel, wich is super easy to do, but as my data has more than 300k rows and growing, I had to split it into different Excel files because the formula is a bit heavy.
After that I import everything to Power BI and finish the calculations there.

But still would be really nice to learn how to solve this using DAX and/or M only, it would also make things simpler.