Forum Discussion

AC23VM's avatar
AC23VM
Helper II
1 year ago
Solved

Help with a problematic measure

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 th...
Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    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()
    )

bhanu_gautam's avatar
bhanu_gautam
Super User
1 year ago

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()
)