Forum Discussion
Calculate the difference between two closest date, and significance difference
I would like to calculate the score difference between the lastest score and its closest past score of the same Name, and then show the difference by show the green up, red down and no change symbol....
amitchandak
Super User
Super UserAnonymous , A new column
var _date= maxx(filter(Table, [Name] = earlier([Name]) && [Date] < earlier([Date]) ), [Date])
return
[score]- maxx(filter(Table, [Name] = earlier([Name]) && [Date] =_date ), [score])
or a measure
anew meausre =
var _date= maxx(filter(allselected(Table) , [Name] = max([Name]) && [Date] < max([Date]) ), [Date])
return
max([score]) - maxx(filter(Table, [Name] = max([Name]) && [Date] =_date ), [score])
or
sum measure
var _date= maxx(filter(allselected(Table) , [Name] = max([Name]) && [Date] < max([Date]) ), [Date])
return
sum([score]) - sumxx(filter(Table, [Name] = max([Name]) && [Date] =_date ), [score])
Thanks for you reply. Do you also know how to solve the same problem but changed to the below tables? Many thanks.
Index Name ID Question Date 1 A 1 Q1_A1 1/9/2022 2 A 2 Q1_A2 3/9/2022 3 C 1 Q1_A5 5/9/2022 4 A 3 Q1_A4 11/9/2022 5 B 1 Q1_A2 18/9/2022 6 C 2 Q1_A3 19/9/2022 7 A 4 Q1_A3 20/9/2022 8 A 5 Q1_A2 21/9/2022 9 B 2 Q1_Q1 22/9/2022 10 C 3 Q1_A1 24/9/2022 Q & Ans no. Score Q1_A1 10 Q1_A2 8 Q1_A3 5 Q1_A4 2 Q1_A5 0