Calculate data within a grouping

[AndreiaCoelho]

Hey, guys!

I've been trying to find a solution to my problem for months.

I have a history of grades assigned by different users (NPS Survey) and I would like to measure the evolution of these grades. I have 3 main data:

1. Respondent's email
2. Grade assigned
3. Response date

I grouped the data by user as in the image

expanding the table for user "Marco", we see that he responded 3 times throughout his journey.

I need to take your last note (most recent note) and compare it to the penultimate note... how to do that?

I managed to pull the last note by the formula

= Table.AddColumn(#"Linhas Agrupadas1", "Personalizar", each Table.Max([Usuário], "Data da criação"))

but I have no idea how to pull the penultimate...

another solution that helps me is: make an average of all user grades.

[JamesRobson]

Hi,

 Probably not the most efficent solution but you could take your data table and get the date with the penultimate dates then use this to get the grade?

= Table.Group(#"Changed Type", {"Email", "Name"}, {{"PenultimateGrade", each List.Sort([Date],Order.Ascending){1}, type nullable date}})

From this

To this

It'll take a bit of work after that to merge back in etc but should be a good jump off point for you.

Thanks,