Trailing Average

[Anonymous]

I have this table:



I was first asked to do a trailing 12 month retention based on service start and end dates which I've already figured out and have on a line graph. I'm now being asked to figure out the trailing 12 month average on the net promoter score for each EMS manager. The net promoter score is simply feedback from the end users where they rate their experience with the EMS manager on a scale of 1-10. So by person, they want to see if their net promoter score is increasing or decreasing over time based on a trailing 12 month average of their scores. How would I go about setting that up? If it helps, here is my current setup I've used to calculate retention based on service start and end dates. I don't know if I can add what I need to these pieces or if I need to calculate this separately:







Which in turn gives me this graph and table:




Thanks!

[v-yuta-msft · Accepted solution]

Hi mrainey,

To achieve your requirement, try DAX formula below:

average score in trailing 12 months =
CALCULATE (
    AVERAGE ( 'EMS Data'[Net Promoter Score] ),
    ALLEXCEPT ( 'EMS Data', 'EMS Data'[EMS] ),
    DATESBETWEEN (
        'Date'[Date],
        LASTDATE ( 'Date'[Date] ),
        EDATE ( LASTDATE ( 'Date'[Date] ), -12 )
    )
)

You can refer to PBIX file here:

https://www.dropbox.com/s/pflcpp5nkjfc3g6/For%20mrainey.pbix?dl=0

If DAX above doesn’t meet your requirement, please share some sample data and clarify your desired results.

Regards,

Jimmy Tao