Forum Discussion

AndreiaCoelho's avatar
AndreiaCoelho
Regular Visitor
3 years ago

Calculate data within a grouping

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.

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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,