Calculate the difference between two closest date, and significance difference

[Anonymous]

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

[Jihwan_Kim]

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.

[Anonymous]

What I mean significance here is testing the statistically significance difference, which mean i have to use the one-tailed t-test

[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]) 

[Anonymous]

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