Counting Same Data that Occurs over Multiple Years

[AnneC]

Hi Everyone

I have a problem, and any help would be appreciated.  In our organisation individuals can participate once or multiple times within a year, or alternatively, not at all.  Each individual has a unique identifier.

I am not interested in how many times an individual has participated over a year, just whether they did particpate, but I need to do this over multiple years and they need to be consecutive, so if my data looked like this:

Year	Unique Identifier
2016	A
2016	B
2016	B
2016	D
2016	E
2017	A
2017	B
2017	C
2017	C
2017	D
2017	E
2018	A
2018	B
2018	C
2018	D
2018	D
2018	D
2018	D
2019	A
2019	A
2019	B
2019	D
2019	E

The result for 2016-2019 would be:

Unique Identifier	2016-2019 Count
A	4
B	4
C	0
D	4
E	0

However, if I looked at only 2017-2018 in my slicer, then the results would be:

Unique Identifier	2017-2018 Count
A	2
B	2
C	2
D	2
E	0

Any assistance would this problem would be greatly appreciated.  Thanks in advance.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[ElenaN]

Hello,

You can try this:

Measure =

VAR YearCount = COUNTROWS(FILTERS(Table3[Year]))

RETURN

IF(DISTINCTCOUNT(Table3[Year]) = YearCount, YearCount, 0)

Regards,

ElenaN