Counting Duplicates

one3nine0

Board Regular
Joined
Jul 21, 2014
Messages
127
I have a table in PowerPivot that has a list of Policy Numbers and Reporting Months. I am trying to count the number of Policy Numbers that show up in the next month.

For Instance, in the table below, I want the output to say in January to say "3" (There was one policy number that duplicated twice, and one policy that duplicated once). It only counts the next months policy numbers.

Reporting DatePolicy Number
1/1/2015TEIKI23
1/1/2015FKKI45
2/1/2015TEIKI23
2/1/2015TEIKI23
2/1/2015FKKI45

<tbody>
</tbody>

Once I have this setup, I will need to do the same, but to count in the next two months instead of just one.

Any ideas?
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
You want to create a measure right (not a calc column)?

First of all, your sample table's column Table[Reporting Date] should be related to Date table column 'Date'[Date].

Then, if you have a pivot table with say Month (from the 'Date' table) on the rows, a measure like this should work:

Code:
Count of Policy Numbers appearing this month that show up next month := 
CALCULATE (
    COUNTROWS ( 'Table' ),
[COLOR=#ff0000][B]    NEXTMONTH ( 'Date'[Date] ),[/B][/COLOR]
    VALUES ( 'Table'[Policy Number] )
)


  • The red NETXMONTH bit can be modified to change the 'Date' filter context as required.
  • VALUES( 'Table'[Policy Number] ) ensures that Policy Numbers currently visible are retained as a filter when counting rows next month.
 
Last edited:
Upvote 0
This works great!!

Is there a way to change the NextMonth function so that it includes two months rather than just 1? IE reporting date as 1/1/2015 but it will count any Policy Number found in 2/1/2015 AND 3/1/2015?
 
Upvote 0
That's good.

To change it to 'the next two months', I am sure there are multiple ways of doing this but here is one:

Change NEXTMONTH( 'Date'[Date] ) to:
Code:
[COLOR=#ff0000][B]DATESINPERIOD ( 'Date'[Date], ENDOFMONTH ( 'Date'[Date] ) + 1, 2, MONTH )[/B][/COLOR]
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top