topic Re: Help Needed : Incremental Counting of calls within 7 Days in power BI with Excel Reference in DAX Commands and Tips

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

Anonymous — Mon, 21 Oct 2024 16:09:02 GMT

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=100159507080639555743&rtpof=true&sd=true

Thanks in advance!

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

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

parry2k — Mon, 21 Oct 2024 19:14:02 GMT

Anonymous 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

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

Ashish_Mathur — Tue, 22 Oct 2024 00:00:46 GMT

Hi,

PBI file attached.

Hope this helps.

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

Anonymous — Tue, 22 Oct 2024 02:45:05 GMT

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:

For calls that occur within the 7-day window, there should be an incremental count.
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!

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

Ashish_Mathur — Tue, 22 Oct 2024 03:14:36 GMT

Hi,

PBI file attached.

Hope this helps.

topic Re: Help Needed : Incremental Counting of calls within 7 Days in power BI with Excel Reference in DAX Commands and Tips

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

Sample Data:

Desired Output:

Excel Formula:

Current DAX Formula (For Calculated Column)

I'm attaching the link for the sample data file.Link :- https://docs.google.com/spreadsheets/d/1UrX49kV6zfVzON3KEtdutjcRSYjbrupZ/edit?usp=sharing&ouid=100159507080639555743&rtpof=true&sd=true

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

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

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

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

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

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