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 NamePromotersDetractorsTotalNPSRolling promotersRolling detractorsRolling totalRolling promoters %Rolling detractors %Rolling NPSjanuari1002015053%1002015067%13%53%februari1503020060%2505035071%14%57%maart2004025064%4509060075%15%60%april2505030067%70014090078%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 NamePromotersDetractorsTotalNPSRolling promotersRolling detractorsRolling totalRolling promoters %Rolling detractors %Rolling NPSjanuari2004025064%2004025080%16%64%februari2505030067%4509055082%16%65% Current measure:Rolling Measure YTD =VAR SelectedYear = SELECTEDVALUE(DateTable[Year])VAR SelectedDate = MAX(DateTable[Date])VAR YTDStartDate = DATE(SelectedYear, 1, 1) RETURNCALCULATE( [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')RETURNDIVIDE(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.

zh1988

Regular Visitor

1 year ago

Solved

Help with measure for rolling NPS

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

1 year ago

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
1 year ago
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
)
)

😃😃

3 Replies

BITomS
Solution Supplier
1 year ago
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)

MohamedFowzan1

Super User

1 year ago

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
Regular Visitor
1 year ago
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
)
)

😃😃

Forum Discussion

Help with measure for rolling NPS

3 Replies

Related content

Rolling 12M Average measure

Rolling accumulation

Rolling accumulation

Matrix Row Filtering Alters Rolling DATESINPERIOD Measures

Rolling performance measure