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 |