Help with a problematic measure

[AC23VM]

Hi there. Could someone please help me with a problematic measure? A while ago, I asked for some help here to determine the top 5 values of a column from the past 3 months. I realised this morning the measure wasn't working at all, when compared to manually filtering. For instance, the measure showed one metric as 31 when it should have been 73, and had it was the first value when it should have been the second.

I messed around a bit with CoPilot, and got a new measure that seemed to get the numbers to match a filtered table, but noticed one of the values was 2 out. I can't figure out why - if it's just a coincidence the rest of the values seem to match - as the measure is counting 4 months (Oct-Jan) and my filters are counting 3 (Nov-Jan). 

I'm trying to determine the top 5 values over the past 3 months before current month, i.e., if stakeholders look in Feb, it'll show Nov-Jan, if they look in Sept, it'll show June-Aug, etc.

I created a mock pbix to show what I mean, but unfortunately I can't upload it anywhere due to my workplace restrictions.  I'll do my best to illustrate:

CD Sub Root Cause Last3MonthsCount =

CALCULATE(

    COUNT('Sheet1'[Consumer Duty Sub Root Cause]),

    DATESINPERIOD(

        'Sheet1'[Date Completed],

        MAX('Sheet1'[Date Completed]),

        -3,

        MONTH

    )

)

CDSubRootCauseTop5Values =

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

VAR FilteredTable =

    CALCULATETABLE(

        VALUES('Sheet1'[Consumer Duty Sub Root Cause]),

        NOT 'Sheet1'[Consumer Duty Sub Root Cause] IN ExcludedValues

    )

VAR RankValue =

    RANKX(

        FilteredTable,

        [CD Sub Root Cause Last3MonthsCount],

        ,

        DESC,

        DENSE

    )

RETURN

IF(

    RankValue <= 5,

    [CD Sub Root Cause Last3MonthsCount],

    BLANK()

)

To note, I've cleaned up the data source since, so there's only NA, but even then I still have to filter it out of CoPilot's measure that's supposed to exclude it.

[DataNinja777]

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,

[bhanu_gautam · Accepted solution]

@AC23VM Measure to count occurrences in the last 3 months excluding the current month:

DAX
CDSubRootCauseLast3MonthsCount =
CALCULATE(
COUNT('Sheet1'[Consumer Duty Sub Root Cause]),
DATESINPERIOD(
'Sheet1'[Date Completed],
EOMONTH(TODAY(), -1), -- End of the previous month
-3,
MONTH
)
)

Measure to get the top 5 values:

DAX
CDSubRootCauseTop5Values =
VAR ExcludedValues = {"NA", "N_A_", "Not Applicable"}
VAR FilteredTable =
CALCULATETABLE(
VALUES('Sheet1'[Consumer Duty Sub Root Cause]),
NOT 'Sheet1'[Consumer Duty Sub Root Cause] IN ExcludedValues,
DATESINPERIOD(
'Sheet1'[Date Completed],
EOMONTH(TODAY(), -1), -- End of the previous month
-3,
MONTH
)
)
VAR RankValue =
RANKX(
FilteredTable,
[CDSubRootCauseLast3MonthsCount],
,
DESC,
DENSE
)
RETURN
IF(
RankValue <= 5,
[CDSubRootCauseLast3MonthsCount],
BLANK()
)