topic Re: Unduplicated (Distinct) Headcount by Date & Cumulative Unduplicated (Distinct) Headcount as of Date in DAX Commands and Tips

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

Anonymous — Fri, 10 Jan 2020 15:59:38 GMT

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

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

Anonymous — Fri, 10 Jan 2020 23:30:40 GMT

Hi Anonymous ,

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

regards,

Jan

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

Anonymous — Fri, 18 Feb 2022 00:54:09 GMT

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

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

tamerj1 — Sat, 19 Feb 2022 05:07:47 GMT

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