Count one ID per year, but display by month?

[nfradkin]

Hi, this should be an easy one for some of you.

I have a table with the following three fields:

• [Person ID]
• Date (possible dates span a full year)
• Registration (binary variable, 0 or 1)

There are a handful of [Person ID] values that show up twice (with different date values where Registration = 1, indicating two registrations for the same person), but I only want to count each person's first registration (i.e., the earliest date within the year) by displaying [Person ID] counts by month. Hope that makes sense.

My current formula is as follows:

DistinctCount_Of_PersonID_Within_Year = CALCULATE(DISTINCTCOUNT(Table[Person ID]),DATESBETWEEN(Table[Date],DATE(2017,08,01),DATE(2018,07,31)))

This results in the correct total count for the year, but the counts for the individual months still sum to more than the total count displayed (so, I know the duplicate [Person ID] values are being counted). It seems that I've just made a long formula for a regular DISTINTCOUNT function. Note that I'm only using the Registration field to filter out non-registrations. I've also tried a DATESINPERIOD function per this thread, but that doesn't seem to work, either.

I'd appreciate any help here! Thanks in advance.

[v-shex-msft]

Hi @nfradkin,

You can try to use below measure if it suitable for your requirement:

DistinctCount_Of_PersonID_Within_Year =
VAR currDate =
    MAX ( Table[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Table[Person ID] ),
        FILTER (
            ALLSELECTED ( Table ),
            YEAR ( Table[Date] ) = YEAR ( CurDate )
                && MONTH ( Table[Date] ) IN VALUES ( Table[Date].[MonthNo] )
                && Table[Registration ] = 1
        )
    )

Regards,

Xiaoxin Sheng

[nfradkin]

Thanks, @v-shex-msft -- I've tried the measure you suggested, but it doesn't seem to be working. I should probably clarify the following:

• By "year," I don't mean "calendar year," but instead "last 12 months."
• I'd like to count the earliest registration and ignore/discard the rest.

Thanks again!

[v-shex-msft]

HI @nfradkin,

Please provide some sample data with expected result to help us clarify your requirement.

Regards,
Xiaoxin Sheng

[nfradkin]

Sure, @v-shex-msft -- sample data are below, followed by default summary output and desired summary output. Thanks for your help!

Sample data

Person ID	Date	Registration
17678	1/12/2018	1
17678	1/27/2018	0
18268	8/15/2017	1
21771	3/8/2018	1
21771	3/17/2018	0
21771	3/25/2018	0
21771	5/3/2018	0
31154	6/18/2018	1
40952	8/20/2017	0
40952	9/10/2017	0
49875	9/19/2017	1
76931	8/10/2017	1
76980	5/12/2018	1
76980	6/3/2018	0
76980	7/15/2018	0
93245	10/4/2017	1
125320	12/14/2017	1
125320	12/16/2017	0
127926	11/30/2017	1
127926	1/27/2018	0
127926	2/18/2018	1
127926	2/20/2018	0

Sum of Registration by Month (default)

Aug	2
Sep	1
Oct	1
Nov	1
Dec	1
Jan	1
Feb	1
Mar	1
Apr	0
May	1
Jun	1
Jul	0

Sum of Registration by Month (desired)

Aug	2
Sep	1
Oct	1
Nov	1
Dec	1
Jan	1
Feb	0
Mar	1
Apr	0
May	1
Jun	1
Jul	0

[v-shex-msft]

Hi @nfradkin,

You can try to use below column to replace registration column, I write formula to check first time current id appears and replace with Registration value.

Registration2 =
IF (
    COUNTROWS (
        FILTER (
            ALL ( Table ),
            Table[Person ID] = EARLIER ( Table[Person ID] )
                && Table[Date] <= EARLIER ( Table[Date] )
        )
    )
        <= 1,
    [Registration],
    0
)

Regards.

Xiaoxin Sheng

[nfradkin]

Hi, @v-shex-msft-

I think we're getting close! This seems to work sometimes, but not always. I think the issue might have to do with date values because it seems to work in some instances (though I don't immediately recognize a pattern). Using the replacement column formula you suggested, the Registration2 values for both dates where Registration = 1 were converted to 0. But, again, this wasn't always the case... Any other ideas? Thanks so much for your help.

[v-shex-msft]

Hi @nfradkin,

Yes, my formula is based current personal id and date, it will try to find out minimum date of each id and replace with correspond registration value.

For records which not the minimum date, I fully replace them as zero.

Comment:
Highlight part will be replaced with corresponding registration value, remained records' registration value will change to 0

Person ID	Date	Registration
17678	1/12/2018	1
17678	1/27/2018	0
18268	8/15/2017	1
21771	3/8/2018	1
21771	3/17/2018	0
21771	3/25/2018	0
21771	5/3/2018	0
31154	6/18/2018	1
40952	8/20/2017	0
40952	9/10/2017	0
49875	9/19/2017	1
76931	8/10/2017	1
76980	5/12/2018	1
76980	6/3/2018	0
76980	7/15/2018	0
93245	10/4/2017	1
125320	12/14/2017	1
125320	12/16/2017	0
127926	11/30/2017	1
127926	1/27/2018	0
127926	2/18/2018	1
127926	2/20/2018	0

Hope above can help you clarity my formula logic.

Regards,

Xiaoxin Sheng

[nfradkin]

Thanks, @v-shex-msft. I think I know what's happening on my end now. What if I instead wanted to use the minimum date after a certain date OR within the last 12 months? Thanks again!!

[v-shex-msft]

Hi @nfradkin,

You can add a condition to set up minimum date filter range to 12 month ago of current date:

Registration2 =
IF (
    COUNTROWS (
        FILTER (
            ALL ( Table1 ),
            VAR currDate = EARLIER ( Table1[Date] ) RETURN [Person ID]
                = EARLIER ( [Person ID] )
                && [Date] <= currDate
                && [Date]
                    >= DATE ( YEAR ( currDate ) - 1, MONTH ( currDate ), DAY ( currDate ) )
        )
    )
        <= 1,
    [Registration],
    0
)

Regards,

Xiaoxin Sheng