Unduplicated (Distinct) Headcount by Date & Cumulative Unduplicated (Distinct) Headcount as of Date

[Anonymous]

Hi,

New to PowerBI, I am tasked with reporting two measures: Unduplicated (Distinct) Headcount by Date & Cumulative Unduplicated Headcount as of Date.

I am working with enrollment data in which students (ColleagueID) can add and drop multiple courses throughout the registration period.

Thank you in advance for your assistance.  Following is an example of the input data:

acadCredID	colleagueID	Course	Name	Status	Date
1234MATH	1234	MATH	LESLIE	ADD	11/20/2019
1234ENGL	1234	ENGL	LESLIE	ADD	11/20/2019
1234MATH	1234	MATH	LESLIE	DROP	11/20/2019
1234ENGL	1234	ENGL	LESLIE	DROP	11/20/2019
1235CHEM	1235	CHEM	JAKE	ADD	11/20/2019
1234MATH	1234	MATH	LESLIE	ADD	11/21/2019
1236ACCT	1236	ACCT	MADDIE	ADD	11/22/2019
1237CHEM	1237	CHEM	BRETT	ADD	11/22/2019
1237ACCT	1237	ACCT	BRETT	ADD	11/22/2019
1236MATH	1236	MATH	MADDIE	ADD	11/23/2019
1236CHEM	1236	CHEM	MADDIE	ADD	11/23/2019
1237MATH	1237	MATH	BRETT	ADD	11/23/2019
1234MATH	1234	MATH	LESLIE	DROP	11/23/2019

Following is an example of the desired reporting result:

Date	Daily Unduplicated Headcount	Cumulative Unduplicated Headcount
11/20/2019	1	1
11/21/2019	1	2
11/22/2019	2	4
11/23/2019	0	3

A

[tamerj1]

Hi @Anonymous ,
Please refer to link to download the sampel file with solution https://www.dropbox.com/t/TsYYUdiIA2R9Sh3g
Mesures are

Daily Unduplicated Headcount = 
VAR AddedList =
    SELECTCOLUMNS (
        FILTER ( 
            Registration,
            Registration[Status] = "ADD"
        ),
        "@ID", Registration[colleagueID]
    )
VAR AddedCount =
    COUNTROWS ( DISTINCT ( AddedList ) )
VAR DroppedList =
SELECTCOLUMNS (
    FILTER ( 
        Registration,
        Registration[Status] = "DROP"
    ),
    "@ID", Registration[colleagueID]
)
VAR DroppedCount =
    COUNTROWS ( DISTINCT ( DroppedList ) )
VAR Result =
    AddedCount - DroppedCount
RETURN
    Result

And

Cumulative Unduplicated Headcount = 
VAR CurrentDate = MAX ( Registration[Date] )
VAR Result =
    CALCULATE ( 
        SUMX ( 
            VALUES ( Registration[Date] ),
            [Daily Unduplicated Headcount1]
        ),
            Registration[Date] <= CurrentDate
    )
RETURN
    Result 

Please let me know if this satisfies your requirement. If so please consider marking this reply as accepted. Thank you

[Anonymous]

Did you figure this one out? I have the same question. Thanks

[Anonymous]

Hi @Anonymous , 

Can you explain why the Daily Unduplicated Headcount  = 0 for the 23-nov-2019?

regards, 

Jan