Graph with average up to a date

[Akka]

Hi,

I have a Net promoter score-table with the entries from customers. The table has two variables, date for the reply and the value for the feedback:

Date_rec (dd.mm.yy)	NPS_Score
01.01.24	10
05.01.24	6
01.02.24	10
02.02.24	7
03.03.24	5
14.03.24	6

My goal is to create a graph with dates on the x-axis and the average up to a given date on the Y-axis. In other words for 01.01 the graph value should be 10. On 05.01. it would be (10+6/2)= 8. On 01.02 it should be (10+6+10)/3= 8.67.

I've tried creating a graph with using Date_rec on the X-axis and the following measure:

Average_NPS= CALCULATE(

    AVERAGE(RH[NPS_Score]),

    FILTER(

        RH,

        YEAR(RH[Date_Rec] = YEAR(TODAY())

            && RH[Date_Rec] <= TODAY()

    )

))

This does not give me the desired results. Could anyone please help?

[Anonymous · Accepted solution]

HI @Akka,

It seems like a common multiple level aggregation in Dax expression, you can try to use the following measure formula if this help with your scenario:

Average_NPS =
VAR currDate =
    MAX ( RH[Date] )
RETURN
    AVERAGEX (
        FILTER (
            SUMMARIZE ( ALLSELECTED ( RH ), [Date], "Total", SUM ( RH[NPS_Score] ) ),
            YEAR ( RH[Date_Rec] ) = YEAR ( currDate )
                && RH[Date_Rec] <= currDate
        ),
        [Total]
    )

Regards,

Xiaoxin Sheng