Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Finding Consecutive number and counting

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 ...
Need Help
v-henryk-mstf's avatar
v-henryk-mstf
Community Support
3 years ago

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.