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. Also, I would like to test whether the change has a significant change in the difference when comparing it, and show it as a filled circle and non-filled circle. Below is the small sample data.
| ID | Name | Score | Date |
| 1 | A | 7 | 1/9/2022 |
| 2 | B | 8 | 3/9/2022 |
| 3 | C | 9 | 5/9/2022 |
| 4 | A | 6 | 11/9/2022 |
| 5 | B | 8 | 18/9/2022 |
| 6 | C | 4 | 19/9/2022 |
| 7 | A | 9 | 20/9/2022 |
| 8 | A | 10 | 21/9/2022 |
| 9 | B | 6 | 22/9/2022 |
| 10 | C | 10 | 24/9/2022 |
4 Replies
- Jihwan_Kim
Super User
Hi,
I tried to create something like below.
When searching for UNICHAR symbols, I could not find what you want, but I hope you can find ones.
Please check the below picture and the attached pbix file.
I hope the below can provide some ideas on how to create a solution for your actual data model.
All measures are in the attached pbix file.
- amitchandak
Anonymous , 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
