Dynamic NPS which upgrade in time

[michelebu]

Dear Community, 
I am trying to build up a graph (or a table) showing how the NPS (Net promoter score) value for a product (called DuF) changes over time.

In my dataset I have a column showing the day and one for the exact time when the feedabck was given.
I also have a separate column for the ratings (scala from 0 to 10).

Methode	Produkt	day	time	rating
Dynamic	DuF	06.03.2024	20:03:28	5
Dynamic	DuF	12.03.2024	11:51:53	4
Dynamic	DuF	12.03.2024	14:21:10	10
Dynamic	DuF	12.03.2024	17:44:03	8
Dynamic	DuF	12.03.2024	18:44:44	5
Dynamic	DuF	13.03.2024	09:01:28	4
Dynamic	DuF	13.03.2024	09:05:23	0
Dynamic	DuF	13.03.2024	12:10:53	10
Dynamic	DuF	13.03.2024	00:17:32	10
Dynamic	DuF	14.03.2024	08:29:17	8
Dynamic	DuF	14.03.2024	10:46:58	10
Dynamic	DuF	14.03.2024	12:37:09	8
Dynamic	DuF	14.03.2024	13:10:36	9
Dynamic	DuF	14.03.2024	16:36:43	10
Dynamic	DuF	14.03.2024	17:39:22	10
Dynamic	DuF	14.03.2024	18:12:41	0
Dynamic	DuF	15.03.2024	10:24:37
Dynamic	DuF	16.03.2024	07:57:53	5
Dynamic	DuF	18.03.2024	10:28:30	8
Dynamic	DuF	18.03.2024	13:24:37	9
Dynamic	DuF	18.03.2024	19:30:32	1
Dynamic	DuF	19.03.2024	16:28:22	9
Dynamic	DuF	19.03.2024	18:06:51	2
Dynamic	DuF	20.03.2024	12:04:42	10
Dynamic	DuF	21.03.2024	06:10:17	10
Dynamic	DuF	25.03.2024	10:49:55	5
Dynamic	DuF	26.03.2024	17:47:35	8
Dynamic	DuF	27.03.2024	16:55:33	5
Dynamic	DuF	30.03.2024	23:14:52	6

In order to calculate the NPS I follow this simple formula: NPS = % Promoter - % Detractors.
Promoter are the values between 9 and 10 and detractors are the values between 0 and 7. The values between 7 and 8 are uselless (the so called "passive").

The Dax I am using is the following: 

NPS_Dynamic =

VAR TotalPromoters =

    CALCULATE(

        COUNTROWS('DuF_NPS'),

        'DuF_NPS'[rating] >= 9 && 'DuF_NPS'[rating] <= 10

    )

VAR TotalDetractors =

    CALCULATE(

        COUNTROWS('DuF_NPS'),

        'DuF_NPS'[rating] >= 0 && 'DuF_NPS'[rating] <= 6

    )

VAR TotalFeedbacks = COUNTROWS('DuF_NPS')

VAR PercentagePromoters = DIVIDE(TotalPromoters, TotalFeedbacks)

VAR PercentageDetractors = DIVIDE(TotalDetractors, TotalFeedbacks)

VAR RawNPS = PercentagePromoters - PercentageDetractors

RETURN

    IF(TotalFeedbacks > 0, RawNPS * 100, BLANK())

It works fine, but as you can see from the table and the line graph, each value represents the NPS calculated on the values collected on a single day. It does not update the value based on the data collected on a previous day.

So what I need is a formula that calculates the daily NPS (if there is a new value) but output the average NPS calculated on all values collected till that point in time. 
For example, on day 1 I have only a value (5). In this case my NPS is -100.
On day 2, I have the following rates: 4, 10, 8, 5. On this day the NPS is -25.
But the OVERALL NPS is -40 because I need to take into account also the value (5) recoded the day before. 
On day 3 we observe the following: 4, 0, 10, 10. Daily NPS is 0 but the average, which should be shown in the graph, is now -22.

For the line graph I use day on X, and NPS_Dynamic on Y.
Any help will be greatly appreciated.
Thank you very much.
Michele

[aduguid]

NPS_Dynamic =
VAR TotalPromoters =
    CALCULATE(
        COUNTROWS('DuF_NPS'),
        'DuF_NPS'[rating] >= 9 && 'DuF_NPS'[rating] <= 10
    )
VAR TotalDetractors =
    CALCULATE(
        COUNTROWS('DuF_NPS'),
        'DuF_NPS'[rating] >= 0 && 'DuF_NPS'[rating] <= 6
    )
VAR TotalFeedbacks = COUNTROWS('DuF_NPS')
VAR PercentagePromoters = DIVIDE(TotalPromoters, TotalFeedbacks)
VAR PercentageDetractors = DIVIDE(TotalDetractors, TotalFeedbacks)
VAR RawNPS = PercentagePromoters - PercentageDetractors
VAR DailyNPS = IF(TotalFeedbacks > 0, RawNPS * 100, BLANK())
VAR CumulativeNPS =
    AVERAGEX(
        FILTER(
            ALL('DuF_NPS'),
            'DuF_NPS'[date] <= MAX('DuF_NPS'[date])
        ),
        [NPS_Dynamic]
    )
RETURN
    IF(TotalFeedbacks > 0, DailyNPS, CumulativeNPS)

[michelebu]

Thank you @aduguid but it seems the formula has a problem.

You have added a circular function by using NPS_Dynamic in the AVERAGEX. 
And this prevents the formula to run.
Do you have another suggestion?
Thank you.

[aduguid]

Sorry about that. I thought I'd try to solve it on my phone. I'll give it another go tomorrow.