Comparison of two data points with different submission date

[ivvan_smo]

Dear all, 

I have following dataset:

Title	Value	Submission date
Test 1	100	6/1/2024
Test 2	200	6/1/2024
Test 3	300	6/1/2024
Test 1	100	6/2/2024
Test 2	350	6/2/2024
Test 3	400	6/2/2024
Test 4	250	6/2/2024
Test 2	200	6/3/2024
Test 3	400	6/3/2024
Test 5	200	6/3/2024

Ideally I would like to create a comparison in a table showing all the projects available in the data set with corresponding value at last submission date and difference vs. submission chosen in Slicer. For example, If I choose my comparison date to 6/1/2024, the outcome should look like this.

Title	Last submission	Diff vs last submission
Test 1	0	-100
Test 2	200	0
Test 3	400	100
Test 4	0	0
Test 5	200	200

Thanks  a lot for your help in advance. 

[Anonymous · Accepted solution]

Thanks for the reply from@ryan_mayu , please allow me to provide another insight:
Hi,@ivvan_smo 

1.First use the calculation table:

Table 2 = DISTINCT('Table'[Title])

Table 3 = VALUES('Table'[Submission date])

The column in table3 is used as a slicer to prevent the slicer from affecting the measure and calculation column.

2.Next create relationships between tables:

3.Create calculated column references:

Last submission = 
VAR cc=CALCULATE(SUM('Table'[Value]),'Table'[Submission date]=MAX('Table'[Submission date]))
RETURN IF(cc=BLANK(),0,cc)
 

4. Below are the measure I've created for your needs:

Diff vs last submission = 
VAR select111=SELECTEDVALUE('Table 3'[Submission date])
VAR now11=CALCULATE(SUM('Table'[Value]),'Table'[Submission date]=select111)
RETURN MAX('Table 2'[Last submission])-now11

5.Here's my final result, which I hope meets your requirements.

[ivvan_smo]

see the screenshot of the data for better understanding of it.

[ryan_mayu]

@ivvan_smo 

pls see the attachment below