Display count of feedbacks if it appeared consecutively 3 or more weeks in a row

[NG1407]

Need to count the number of only those feedbacks that appeared 3 or more consecutive weeks in a row. I have a table that has below structure:

feedback	date
other	10/6/2024
other	10/13/2024
other	10/20/2024
other	10/27/2024
Bad	11/3/2024
Bad	11/10/2024
Bad	12/1/2024
Bad	12/8/2024
Poor	11/3/2024
Poor	11/10/2024
Poor	12/1/2024
Poor	12/8/2024
Poor	12/15/2024
Poor	12/22/2024
Poor	10/27/2024
Good	11/1/2024
Good	11/2/2024
Good	11/3/2024
Good	11/4/2024
Good	11/9/2024
Good	11/10/2024
Good	11/17/2024
Good	11/23/2024
Good	11/24/2024
Good	11/25/2024
Good	11/26/2024
Good	11/27/2024
Good	12/8/2024

If a feedback is on the table 3 weeks in a row, it will have a count of 3 and if it stays on the report next week too then count will be 4. 

[NG1407]

@bhanu_gautam its not allowing to cretae measure it says 

[bhanu_gautam]

@NG1407 , Since earlier is giving issue try using 

ConsecutiveFeedbackCount =
SUMX (
ADDCOLUMNS (
SUMMARIZE (
'YourTable',
'YourTable'[feedback],
'YourTable'[date]
),
"ConsecutiveCount",
VAR CurrentFeedback = 'YourTable'[feedback]
VAR CurrentDate = 'YourTable'[date]
VAR ConsecutiveWeeksCount =
CALCULATE (
COUNTROWS (
FILTER (
'YourTable',
'YourTable'[feedback] = CurrentFeedback &&
'YourTable'[date] <= CurrentDate &&
'YourTable'[date] >= CurrentDate - 21
)
)
)
RETURN
ConsecutiveWeeksCount
),
IF ([ConsecutiveCount] >= 3, 1, 0)
)

[NG1407]

I tried it and its still not giving correct result. its just giving 0

[Anonymous · Accepted solution]

Hi @NG1407 ,

I suggest you to refer to my attatchment to have a try.

Here I create a DimDate table to help calcualtion.

DimDate = 
ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",MONTH([Date]),"WeekBin(Start)",[Date] - WEEKDAY([Date],1)+ 1)

Measure:

MEASURE = 
VAR _GENERATE =
    GENERATE (
        CALCULATETABLE ( VALUES ( DimDate[WeekBin(Start)] ), ALL ( DimDate ) ),
        CALCULATETABLE ( VALUES ( 'Table'[Feedback] ), ALLSELECTED ( 'Table' ) )
    )
VAR _CHECK_In_Bin =
    ADDCOLUMNS (
        _GENERATE,
        "Check In Bin",
            VAR _Feedback = [Feedback]
            VAR _WEEKLIST =
                CALCULATETABLE (
                    VALUES ( 'DimDate'[WeekBin(Start)] ),
                    FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Feedback] = _Feedback )
                )
            RETURN
                IF ( [WeekBin(Start)] IN _WEEKLIST, 1, 0 )
    )
VAR _ADDGroup =
    FILTER (
        ADDCOLUMNS (
            _CHECK_In_Bin,
            "MaxDate",
                VAR _Feedback = [Feedback]
                VAR _WeekBin = [WeekBin(Start)]
                RETURN
                    MAXX (
                        FILTER (
                            _CHECK_In_Bin,
                            [Feedback] = _Feedback
                                && [Check In Bin] <> 1
                                && [WeekBin(Start)] <= _WeekBin
                        ),
                        [WeekBin(Start)]
                    )
        ),
        [Check In Bin] = 1
    )
VAR _ADDSUM =
    ADDCOLUMNS (
        _ADDGroup,
        "SUM",
            VAR _Feedback = [Feedback]
            VAR _MaxDate = [MaxDate]
            RETURN
                SUMX (
                    FILTER ( _ADDGroup, [Feedback] = _Feedback && [MaxDate] = _MaxDate ),
                    [Check In Bin]
                )
    )
RETURN
    SUMX (
        FILTER (
            _ADDSUM,
            [WeekBin(Start)] = MAX ( DimDate[WeekBin(Start)] )
                && [Feedback] = MAX ( 'Table'[Feedback] )
        ),
        [SUM]
    )

Result is as below.

Best Regards,
Rico Zhou

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

[bhanu_gautam]

@NG1407 , For this first make sure you have one date table if not create using 

DateTable =
ADDCOLUMNS (
CALENDAR ( MIN('YourTable'[date]), MAX('YourTable'[date])),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"Week", WEEKNUM([Date], 2) -- Week starts on Monday
)

Add a calculated column to your main table to get the week number:

WeekNumber = WEEKNUM('YourTable'[date], 2) -- Week starts on Monday

Add a calculated column to identify consecutive weeks:

ConsecutiveWeeks =
VAR CurrentFeedback = 'YourTable'[feedback]
VAR CurrentDate = 'YourTable'[date]
VAR PreviousDate =
CALCULATE (
MAX('YourTable'[date]),
FILTER (
'YourTable',
'YourTable'[feedback] = CurrentFeedback &&
'YourTable'[date] < CurrentDate
)
)
VAR PreviousWeekNumber = WEEKNUM(PreviousDate, 2)
VAR CurrentWeekNumber = WEEKNUM(CurrentDate, 2)
RETURN
IF (
CurrentWeekNumber - PreviousWeekNumber = 1,
1,
0
)

Create a measure to count the number of feedbacks that appeared 3 or more consecutive weeks:

ConsecutiveFeedbackCount =
SUMX (
ADDCOLUMNS (
SUMMARIZE (
'YourTable',
'YourTable'[feedback],
'YourTable'[date]
),
"ConsecutiveCount",
CALCULATE (
COUNTROWS (
FILTER (
'YourTable',
'YourTable'[feedback] = EARLIER('YourTable'[feedback]) &&
'YourTable'[date] <= EARLIER('YourTable'[date]) &&
'YourTable'[date] >= EARLIER('YourTable'[date]) - 21
)
)
)
),
IF ([ConsecutiveCount] >= 3, 1, 0)
)