Finding Consecutive number and counting

[Anonymous]

Hi,

I have attached data set and expected output as well, but not sure how to get the perfect DAX to find the consecutive number and counting.

Pls refer the below and let me know how is this feasible.

Sr No	Date	Name	Status
1	1/11/2022	ABC	Present
2	2/11/2022	ABC	Present
3	3/11/2022	ABC	Absent
4	4/11/2022	ABC	Absent
5	5/11/2022	ABC	Absent
6	5/12/2022	ABC	Absent
7	5/13/2022	ABC	Present
8	5/14/2022	ABC	Absent
9	5/15/2022	ABC	Absent
10	1/11/2022	DEF	Present
11	2/11/2022	DEF	Absent
12	3/11/2022	DEF	Absent
13	4/11/2022	DEF	Present
14	5/11/2022	DEF	Present
15	5/12/2022	DEF	Absent
16	5/13/2022	DEF	Absent
17	5/14/2022	DEF	Present
18	5/15/2022	DEF	Present

Name	Consecutive Absent 2	Consecutive Absent 3	Consecutive Absent 3 plus
ABC	1	0	1
DEF	2	0	0

Thanks,

[Anonymous]

I want to see consecutive count of number of absent like total number of absent by student absent continiously 2 times, 3 times and 3 plus times. so that I can focus on the student who are absent frequently continiously.

Ex: ABC is continiously absent 4 times from March 11 to May 12. Absent 2 times from May 14th May 15

         3 plus   2 times

ABC      1          1

DEF       0          2

Any solution pls.

[v-henryk-mstf]

Hi @Anonymous ,

Do you want to count the number of consecutive absences or the length of each absence, grouped by name? Would the following formula be helpful to you.

a =
VAR next_ =
    TOPN (
        1,
        FILTER (
            'Table',
            'Table'[Sr No] < EARLIER ( 'Table'[Sr No] )
                && 'Table'[Name] = EARLIER ( 'Table'[Name] )
        ),
        [Sr No], DESC
    )
VAR next_status =
    MINX ( next_, [Status] )
RETURN
    IF ( 'Table'[Status] = "Absent" && 'Table'[Status] = next_status, "yes", "no" )

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.