Find the difference between aggregated averages to show trend

[nrj1806]

Hi All,

I have table, that has surveys since last 2 years. I would like to aggregate the NPS column based on date (Please note the surveys were answered on the same day). I need to show an upward arrow if the average NPS increased(greater than 0) from the latest survey as compared to the previous, downward arrow if its the opposite(the difference is less than 0). 

How to add dynamic arrows depending on the measure value in Power BI - YouTube shows how to create the arrows. But I am finding difficulty in finding the difference in the average between the last two surveys.

Sample data below:

 OCT 1 average NPS is 7.5.

APR 1 average NPS is 8.

So the NPS has increased.

I have the latest average using formula:

AVERAGE NPS = VAR endDate = LASTDATE('POWERBISAMPLE'[STARTTIME])

RETURN

CALCULATE(

    AVERAGE('POWERBISAMPLE'[STARTTIME]), 'POWERBISAMPLE'[STARTTIME] = endDate

)

How do i find the avergae of the last calendar value?

How do i write the DAX in such a way that i can find the difference?

I tried using EARLIER function but wasnt able to figure it out well.

[Anonymous · Accepted solution]

Hi @nrj1806,

This seems like a common multiple field date range analysis requirements, you can take a look at the following link about the start, end date range analysis.

Before You Post, Read This: start, end date 

Regards,

Xiaoxin Sheng