Counting instances

[EWBWEBB]

Hi there

I am trying to count the instances of Covid over a period of time.

There are two piece to this.

1.) how do I count the instances where the first instance would be labelled 1 and the second as 2, third as 3 and so on. Using earlier seems to mark them all as the highest count. so the first second and third would all show a count of 3.

2.) I'd like to do this to see if the number of multiple instances has changed year on year. So count the first one in the year as 1, second as 2 then the next year restart the count at 1, and so on.

My data is laid out as follows 

From Date	UNID
19/03/2020	F005F
15/11/2020	F0040
20/01/2021	Y8F44
01/03/2021	F00FF
04/03/2021	F0078
09/06/2021	Y8F08
30/06/2021	F005F
20/07/2021	a0588
30/07/2021	F00FF
14/09/2021	F00FF
25/09/2021	F00FF
19/11/2021	F0090
12/12/2021	Y8F44
03/01/2022	a058F
23/05/2022	a0584
01/06/2022	a0588
05/07/2022	F00FF
11/07/2022	F0078

I have a caelndar (simple just every day of the year) joined on from date 1-Many.

This would be my expected result.

		expected result
		Total Count	In Year Count
From Date	UNID
19/03/2020	F005F	1	1
15/11/2020	F0040	1	1
20/01/2021	Y8F44	1	1
01/03/2021	F00FF	1	1
04/03/2021	F0078	1	1
09/06/2021	Y8F08	1	1
30/06/2021	F005F	2	1
20/07/2021	a0588	1	1
30/07/2021	F00FF	2	2
14/09/2021	F00FF	3	3
25/09/2021	F00FF	4	4
19/11/2021	F0090	1	1
12/12/2021	Y8F44	2	2
03/01/2022	a058F	1	1
23/05/2022	a0584	1	1
01/06/2022	a0588	2	1
05/07/2022	F00FF	5	1
11/07/2022	F0078	2	1

From this I would then like to be able to count the number of people with multiple instances in each year and the average number of instances - which should be pretty straight forward if this is a calculated column. 

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

Total count CC = 
COUNTROWS (
    FILTER (
        Data,
        Data[UNID] = EARLIER ( Data[UNID] )
            && Data[From Date] <= EARLIER ( Data[From Date] )
    )
)

In year count CC = 
COUNTROWS (
    FILTER (
        Data,
        Data[UNID] = EARLIER ( Data[UNID] )
            && YEAR ( Data[From Date] ) = YEAR ( EARLIER ( Data[From Date] ) )
            && Data[From Date] <= EARLIER ( Data[From Date] )
    )
)

[EWBWEBB]

@Jihwan_Kim This is perfect thank you.

So the next step - Is there a way that just the most recent instance in each year is marked?

I.e for those that have just 1 instance in a year we get a 1, for those that have had 2 instances in a year the first instance in blank and the second is mark as 2 and so on?

When I count this the 1 is throwing the figures off a little as someone with 2 or more instances is also appearing in the column counting their first and second instance as well.

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your reply.

I am not sure if I correctly understood your second questions, but please check the below picture and the attached pbix file whether it suits your requirement.

Expected CC =
VAR _toponebyyeartable =
    TOPN (
        1,
        FILTER (
            Data,
            Data[UNID] = EARLIER ( Data[UNID] )
                && YEAR ( Data[From Date] ) = YEAR ( EARLIER ( Data[From Date] ) )
        ),
        Data[In year count CC], DESC
    )
RETURN
    IF (
        MAXX ( _toponebyyeartable, Data[In year count CC] ) = Data[In year count CC],
        Data[In year count CC]
    )

[EWBWEBB]

Spoth on @Jihwan_Kim - thank you so much

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

Total count CC = 
COUNTROWS (
    FILTER (
        Data,
        Data[UNID] = EARLIER ( Data[UNID] )
            && Data[From Date] <= EARLIER ( Data[From Date] )
    )
)

In year count CC = 
COUNTROWS (
    FILTER (
        Data,
        Data[UNID] = EARLIER ( Data[UNID] )
            && YEAR ( Data[From Date] ) = YEAR ( EARLIER ( Data[From Date] ) )
            && Data[From Date] <= EARLIER ( Data[From Date] )
    )
)