Help with measure for rolling NPS

[zh1988]

Hi there,

I'm trying to build a DAX measure in Power BI to calculate a rolling Net Promoter Score (NPS) based on selected months. I'm having trouble getting the cumulative calculation to work correctly.

Here's my sample data and the desired outcome:

Month Name	Promoters	Detractors	Total	NPS	Rolling promoters	Rolling detractors	Rolling total	Rolling promoters %	Rolling detractors %	Rolling NPS
januari	100	20	150	53%	100	20	150	67%	13%	53%
februari	150	30	200	60%	250	50	350	71%	14%	57%
maart	200	40	250	64%	450	90	600	75%	15%	60%
april	250	50	300	67%	700	140	900	78%	16%	62%

How the Rolling Calculation Should Work:

The "Rolling" columns should calculate the cumulative sum of Promoters, Detractors, and Total for the selected months up to and including the current month. The "Rolling NPS Selected Months" is then calculated based on these rolling totals.

Example:

If the user selects March and April, 

• For April: The rolling values should be the sum of March and April. 
  
  

Month Name	Promoters	Detractors	Total	NPS	Rolling promoters	Rolling detractors	Rolling total	Rolling promoters %	Rolling detractors %	Rolling NPS
januari	200	40	250	64%	200	40	250	80%	16%	64%
februari	250	50	300	67%	450	90	550	82%	16%	65%

Current measure:

Rolling Measure YTD =

VAR SelectedYear = SELECTEDVALUE(DateTable[Year])

VAR SelectedDate = MAX(DateTable[Date])

VAR YTDStartDate = DATE(SelectedYear, 1, 1)

RETURN

CALCULATE(

    [NPS], // 

    FILTER(

        ALL(DateTable),

        DateTable[Date] >= YTDStartDate && DateTable[Date] <= SelectedDate

    )

)

NPS =

VAR Promoters = CALCULATE(COUNTROWS('fact_table'), 'fact_table'[nps score] >= 9)

VAR Detractors = CALCULATE(COUNTROWS('fact_table'), 'fact_table'[nps score] <= 6)

VAR TotalResponses = COUNTROWS('fact_table')

RETURN

DIVIDE(Promoters - Detractors, TotalResponses, 0) * 100

Problem:

My current DAX measure isn't calculating the rolling values correctly. I need help with a DAX measure that will produce the "Rolling" columns and the "Rolling NPS Selected Months" as shown in the table above, based on the user's selected months. Any help would be greatly appreciated! My current measure likely uses a filter context that includes all dates from the beginning of the year up to the last selected month. 

[MohamedFowzan1 · Accepted solution]

Hi @zh1988 

The Basic measures you need:

Promoters =
CALCULATE(
    COUNTROWS('fact_table'),
    'fact_table'[nps score] >= 9
)

Detractors =
CALCULATE(
    COUNTROWS('fact_table'),
    'fact_table'[nps score] <= 6
)

TotalResponses =
COUNTROWS('fact_table')

NPS =
DIVIDE(
    [Promoters] - [Detractors],
    [TotalResponses],
    0
) * 100

 
The Rolling Sums:

RollingPromoters =
VAR CurrentMonthOrder = MAX('DateTable'[MonthNumber])
VAR SelectedMonths =
    CALCULATETABLE(
        VALUES('DateTable'[MonthNumber]),
        ALLSELECTED('DateTable')
    )
VAR MinSelectedMonth = MINX(SelectedMonths, [MonthNumber])
RETURN
CALCULATE(
    [Promoters],
    FILTER(
        ALL('DateTable'),
        'DateTable'[MonthNumber] >= MinSelectedMonth &&
        'DateTable'[MonthNumber] <= CurrentMonthOrder
    )
)

RollingDetractors =
VAR CurrentMonthOrder = MAX('DateTable'[MonthNumber])
VAR SelectedMonths =
    CALCULATETABLE(
        VALUES('DateTable'[MonthNumber]),
        ALLSELECTED('DateTable')
    )
VAR MinSelectedMonth = MINX(SelectedMonths, [MonthNumber])
RETURN
CALCULATE(
    [Detractors],
    FILTER(
        ALL('DateTable'),
        'DateTable'[MonthNumber] >= MinSelectedMonth &&
        'DateTable'[MonthNumber] <= CurrentMonthOrder
    )
)

RollingTotalResponses =
VAR CurrentMonthOrder = MAX('DateTable'[MonthNumber])
VAR SelectedMonths =
    CALCULATETABLE(
        VALUES('DateTable'[MonthNumber]),
        ALLSELECTED('DateTable')
    )
VAR MinSelectedMonth = MINX(SelectedMonths, [MonthNumber])
RETURN
CALCULATE(
    [TotalResponses],
    FILTER(
        ALL('DateTable'),
        'DateTable'[MonthNumber] >= MinSelectedMonth &&
        'DateTable'[MonthNumber] <= CurrentMonthOrder
    )
)

 
Rolling perc & NPS:

RollingPromotersPct =
DIVIDE(
    [RollingPromoters],
    [RollingTotalResponses],
    0
)

RollingDetractorsPct =
DIVIDE(
    [RollingDetractors],
    [RollingTotalResponses],
    0
)

RollingNPS =
DIVIDE(
    [RollingPromoters] - [RollingDetractors],
    [RollingTotalResponses],
    0
) * 100

Please do mention the kind of issue faced or what discrepancy occurs if this doesn't help

[MohamedFowzan1 · Accepted solution]

Hi @zh1988 

The Basic measures you need:

Promoters =
CALCULATE(
    COUNTROWS('fact_table'),
    'fact_table'[nps score] >= 9
)

Detractors =
CALCULATE(
    COUNTROWS('fact_table'),
    'fact_table'[nps score] <= 6
)

TotalResponses =
COUNTROWS('fact_table')

NPS =
DIVIDE(
    [Promoters] - [Detractors],
    [TotalResponses],
    0
) * 100

 
The Rolling Sums:

RollingPromoters =
VAR CurrentMonthOrder = MAX('DateTable'[MonthNumber])
VAR SelectedMonths =
    CALCULATETABLE(
        VALUES('DateTable'[MonthNumber]),
        ALLSELECTED('DateTable')
    )
VAR MinSelectedMonth = MINX(SelectedMonths, [MonthNumber])
RETURN
CALCULATE(
    [Promoters],
    FILTER(
        ALL('DateTable'),
        'DateTable'[MonthNumber] >= MinSelectedMonth &&
        'DateTable'[MonthNumber] <= CurrentMonthOrder
    )
)

RollingDetractors =
VAR CurrentMonthOrder = MAX('DateTable'[MonthNumber])
VAR SelectedMonths =
    CALCULATETABLE(
        VALUES('DateTable'[MonthNumber]),
        ALLSELECTED('DateTable')
    )
VAR MinSelectedMonth = MINX(SelectedMonths, [MonthNumber])
RETURN
CALCULATE(
    [Detractors],
    FILTER(
        ALL('DateTable'),
        'DateTable'[MonthNumber] >= MinSelectedMonth &&
        'DateTable'[MonthNumber] <= CurrentMonthOrder
    )
)

RollingTotalResponses =
VAR CurrentMonthOrder = MAX('DateTable'[MonthNumber])
VAR SelectedMonths =
    CALCULATETABLE(
        VALUES('DateTable'[MonthNumber]),
        ALLSELECTED('DateTable')
    )
VAR MinSelectedMonth = MINX(SelectedMonths, [MonthNumber])
RETURN
CALCULATE(
    [TotalResponses],
    FILTER(
        ALL('DateTable'),
        'DateTable'[MonthNumber] >= MinSelectedMonth &&
        'DateTable'[MonthNumber] <= CurrentMonthOrder
    )
)

 
Rolling perc & NPS:

RollingPromotersPct =
DIVIDE(
    [RollingPromoters],
    [RollingTotalResponses],
    0
)

RollingDetractorsPct =
DIVIDE(
    [RollingDetractors],
    [RollingTotalResponses],
    0
)

RollingNPS =
DIVIDE(
    [RollingPromoters] - [RollingDetractors],
    [RollingTotalResponses],
    0
) * 100

Please do mention the kind of issue faced or what discrepancy occurs if this doesn't help

[zh1988 · Accepted solution]

Hi there, thank you very much!! When first applying the measures it did not take the year slicer into account (I also did not mention that explicitly) so the totals were based on the complete data set, but by adding a month key into allmeasures it now shows correct data. 

RollingPromoters_monthkey =

VAR CurrentMonthKey = MAX('dim_date'[Year]) * 100 + MAX('dim_date'[Month])

VAR SelectedMonthKeys =

    SELECTCOLUMNS(

        FILTER(

            ALLSELECTED('dim_date'),

            NOT(ISBLANK('dim_date'[Month])) &&

            NOT(ISBLANK('dim_date'[Year]))

        ),

        "MonthKey", 'dim_date'[Year] * 100 + 'dim_date'[Month]

    )

VAR MinSelectedMonthKey = MINX(SelectedMonthKeys, [MonthKey])

RETURN

CALCULATE(

    [Promoters],

    FILTER(

        ALL('dim_date'),

        ('dim_date'[Year] * 100 + 'dim_date'[Month]) >= MinSelectedMonthKey &&

        ('dim_date'[Year] * 100 + 'dim_date'[Month]) <= CurrentMonthKey &&

        ('dim_date'[Year] * 100 + 'dim_date'[Month]) IN SelectedMonthKeys

    )

)

😃😃

[BITomS]

Hi @zh1988 ,

Hope this helps:

Rolling NPS Selected Months =
VAR CurrentMonth = MAX('DateTable'[MonthStartDate])
VAR RollingPromoters =
CALCULATE(
COUNTROWS('fact_table'),
'fact_table'[nps score] >= 9,
FILTER(
ALLSELECTED('DateTable'),
'DateTable'[MonthStartDate] <= CurrentMonth
)
)
VAR RollingDetractors =
CALCULATE(
COUNTROWS('fact_table'),
'fact_table'[nps score] <= 6,
FILTER(
ALLSELECTED('DateTable'),
'DateTable'[MonthStartDate] <= CurrentMonth
)
)
VAR RollingTotal =
CALCULATE(
COUNTROWS('fact_table'),
FILTER(
ALLSELECTED('DateTable'),
'DateTable'[MonthStartDate] <= CurrentMonth
)
)
RETURN
DIVIDE(RollingPromoters - RollingDetractors, RollingTotal, 0)