Help Needed : Incremental Counting of calls within 7 Days in power BI with Excel Reference

[Revanth_Reddy]

Hi everyone,

I'm trying to replicate an Excel formula in Power BI that counts the number of calls made within a 7-day window for each Contact Number. I have an Excel file with the relevant data and the formula that works correctly, but I'm struggling to get the same results in Power BI.

Sample Data:

Contact No	Call Date
71201	5/13/2024
2012053298	5/1/2024
2012053298	5/2/2024
2012053298	5/10/2024
2012088102	5/30/2024
2012088102	5/30/2024
2012088102	5/30/2024
2012088102	6/8/2024
2012088102	6/10/2024
2012088623	5/15/2024
2012206282	5/16/2024
2012381333	5/7/2024
2012381333	4/30/2024
2012381430	5/8/2024
2012404924	5/3/2024
2012506103	5/6/2024
2012506103	5/6/2024
2012506103	5/8/2024

 

Desired Output:

The expected output I want in Power BI is:

Contact No	Call Date	Repeat Count with 7 days
71201	5/13/2024	1
2012053298	5/1/2024	1
2012053298	5/2/2024	2
2012053298	5/10/2024	1
2012088102	5/30/2024	1
2012088102	5/30/2024	2
2012088102	5/30/2024	3
2012088102	6/8/2024	1
2012088102	6/10/2024	2
2012088623	5/15/2024	1
2012206282	5/16/2024	1
2012381333	5/7/2024	1
2012381333	4/30/2024	1
2012381430	5/8/2024	1
2012404924	5/3/2024	1
2012506103	5/6/2024	1
2012506103	5/6/2024	2
2012506103	5/8/2024	3

Excel Formula:

In Excel, I am using the following formula:

=COUNTIFS($A$1:A2,$A2,$B$1:B2,"<="&B2,$B$1:B2,">="&INT(B2)-7)

Current DAX Formula (For Calculated Column)

Here is the DAX formula I’m currently trying to implement:

Repeat Count With 7 days  =

VAR Current No = 'Table'[Contact No]

VAR CurrentCallDate = 'Table'[Call Date]

VAR StartDate = CurrentCallDate - 7

RETURN

    COUNTROWS(

        FILTER(

            'Table',

            'Table'[ Contact No  ] = CurrentANI &&

            'Table'[ Call Date  ] <= CurrentCallDate &&

            'Table'[ Call Date  ] >= StartDate

        )

    )

Unfortunately, the DAX formula is not returning the expected incremental counts.

Could anyone help me modify my DAX formula to achieve the desired output?

I'm attaching the link for the sample data file.

Link :- https://docs.google.com/spreadsheets/d/1UrX49kV6zfVzON3KEtdutjcRSYjbrupZ/edit?usp=sharing&ouid=10015...

Thanks in advance!

@amitchandak @Greg_Deckler @lbendlin @Kedar_Pande @rajendraongole1 @Ritaf1983 @parry2k @audreygerred @SamWiseOwl @dharmendars007 @Omid_Motamedise @Jihwan_Kim @danextian @Ashish_Mathur @ibarrau

 

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[Revanth_Reddy]

Hi Ashish,

Thank you for your solution. However, the PBIX result you shared shows the incremental count for all calls based on the contact number. I’m trying to track call repeats over a 7-day window. Here’s what I need:

1. For calls that occur within the 7-day window, there should be an incremental count.
2. If a repeat call occurs after the 7-day period, the count should restart from 1 and then increment from there.

If you check my sample data output file, you’ll see how the 7-day period is applied.

Could you please adjust the PBIX to reflect this logic?

Thank you!

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[parry2k]

@Revanth_Reddy assuming you are adding this as a calculated column, try this:

Repeat Count With 7 days = 
VAR CurrentNo = 'Table'[Contact No]
VAR CurrentCallDate = 'Table'[Call Date]
VAR StartDate = CurrentCallDate - 6
VAR RowsCount = 
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Call Date] ),
        FILTER(
            ALL ( 'Table' ),
            'Table'[Contact No] = CurrentNo &&
            'Table'[Call Date] <= CurrentCallDate &&
            'Table'[Call Date] >= StartDate
        )
    )
RETURN
RowsCount