Help with a problematic measure

Hi AC23VM ,

It looks like the main issue with your measure is how DATESINPERIOD is calculating the last three months and how ranking is applied. The function uses MAX('Sheet1'[Date Completed]), meaning it looks at the latest date in the dataset rather than dynamically determining the current month. This could be causing the issue where October is included instead of just focusing on the last three months before the current one. Another issue is how ranking is being applied. The VALUES function does not fully aggregate the data for ranking, which could lead to incorrect positions. Additionally, filtering out "NA" values isn't being fully applied in your existing approach.

A better way to approach the date filtering is by defining the correct start and end dates dynamically. Instead of relying on MAX('Sheet1'[Date Completed]) alone, we calculate the appropriate range by subtracting three months from the current max date and setting the end date as the last day of the previous month.

CD Sub Root Cause Last3MonthsCount =
VAR CurrentMaxDate = MAX('Sheet1'[Date Completed])
VAR StartDate = EDATE(CurrentMaxDate, -3) 
VAR EndDate = EOMONTH(CurrentMaxDate, -1)

RETURN 
CALCULATE(
    COUNT('Sheet1'[Consumer Duty Sub Root Cause]),
    'Sheet1'[Date Completed] >= StartDate &&
    'Sheet1'[Date Completed] <= EndDate
)

To ensure ranking is applied correctly, it's necessary to summarize the dataset before ranking, rather than using VALUES. This measure creates a summarized table of counts and assigns ranks based on those counts.

CDSubRootCauseTop5Values =
VAR ExcludedValues = {"NA", "N_A_", "Not Applicable"}

VAR FilteredTable =
    ADDCOLUMNS(
        SUMMARIZE(
            'Sheet1',
            'Sheet1'[Consumer Duty Sub Root Cause]
        ),
        "CountValue", [CD Sub Root Cause Last3MonthsCount]
    )

VAR RankedTable =
    ADDCOLUMNS(
        FilteredTable,
        "RankValue",
        RANKX(FilteredTable, [CountValue], , DESC, DENSE)
    )

VAR RankValue =
    LOOKUPVALUE(
        RankedTable[RankValue],
        RankedTable[Consumer Duty Sub Root Cause], 
        SELECTEDVALUE('Sheet1'[Consumer Duty Sub Root Cause])
    )

RETURN
IF(
    RankValue <= 5,
    [CD Sub Root Cause Last3MonthsCount],
    BLANK()
)

This approach ensures that the measure properly filters for the correct three-month period, correctly aggregates the count per sub-root cause, and ranks them accurately. Additionally, the NA filtering is applied consistently, preventing unwanted values from interfering with the ranking process. If stakeholders check the report in February, they will see data for November through January, and if they check in September, they will see data for June through August. Try these measures in your model and compare them to your manually filtered table to verify if the numbers align correctly. Let me know if you need further refinements!

Best regards,