Forum Discussion

Akka's avatar
Akka
Regular Visitor
2 years ago
Solved

Graph with average up to a date

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.2410
05.01.246
01.02.2410
02.02.247
03.03.245
14.03.246
  

 

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?
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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