cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Count the number of people who appear for more than 3 months in a row

I have a Goals table, which only shows the staff who do not have specific goal types in the system. An example below:

 Month Goal Type Person Name Feb-23 Performance Jack Mar-23 Performance Jack Apr-23 Performance Jack Nov-22 Risk John Dec-22 Risk John Sep-23 Risk John

I want to be able to count / show a list of people who appear more than 3 months in a row.

In the example above, Jack would appear on the list in Apr-23, as he was missing a goal for 3 months in a row.

John would not appear on the list in Sep-23 as he has only reappeared for the first time since Dec-22.

I have a Goals table and a Date table.

Thank you.

1 ACCEPTED SOLUTION
Community Support

Hi @melg-9,

You can try to use following measure formula to check the records if they continue and return flag:

``````3M Flag =
VAR currDate =
MAX ( Table1[Month] )
VAR rolling3MCount =
CALCULATE (
COUNTROWS ( Table1 ),
FILTER (
ALLSELECTED ( Table1 ),
[Month]
>= DATE ( YEAR ( currDate ), MONTH ( currDate ) - 3, DAY ( currDate ) )
&& [Month] <= currDate
),
VALUES ( Table1[Person Name] )
)
RETURN
IF ( rolling3MCount >= 3, "Y", "N" )``````

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Community Support

Hi @melg-9,

You can try to use following measure formula to check the records if they continue and return flag:

``````3M Flag =
VAR currDate =
MAX ( Table1[Month] )
VAR rolling3MCount =
CALCULATE (
COUNTROWS ( Table1 ),
FILTER (
ALLSELECTED ( Table1 ),
[Month]
>= DATE ( YEAR ( currDate ), MONTH ( currDate ) - 3, DAY ( currDate ) )
&& [Month] <= currDate
),
VALUES ( Table1[Person Name] )
)
RETURN
IF ( rolling3MCount >= 3, "Y", "N" )``````

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.