Variance between two date values of specific stage

[harshadrokade]

Hi sir,mam,

Thanks for looking into the queruy.

I have data as given below. I want to find the variance between two dates in sequence and show the same in a line chart. How can I achive the same?

I have given the example of output as well as base data

Both Base data tables (Stage table and amount tables are connected with each other with ID column.)

Base data- Stage table

ID	Date	Stage
1	16-10-23	A
2	16-10-23	A
3	16-10-23	A
4	17-10-23	A
5	17-10-23	A
6	17-10-23	A
7	18-10-23	A
8	18-10-23	A
9	18-10-23	A
10	16-10-23	B
11	16-10-23	B
12	16-10-23	B
13	16-10-23	B
14	17-10-23	B
15	17-10-23	B
16	17-10-23	B
17	17-10-23	B
18	18-10-23	B
19	16-10-23	C
20	17-10-23	C
21	17-10-23	C
22	17-10-23	C
23	17-10-23	C
24	17-10-23	C
25	17-10-23	C
26	18-10-23	C
27	18-10-23	C

Base data- Amount table

ID	Amount
1	79
2	38
3	15
4	68
5	16
6	26
7	86
8	84
9	58
10	20
11	63
12	86
13	59
14	21
15	72
16	76
17	52
18	65
19	46
20	18
21	49
22	46
23	37
24	44
25	47
26	78
27	64

Output table visual expected as below

Column A, B and C are calculated as total amount of all IDs that are having respective date and stage. 

Column Variance are calculated by deducting the latest date value from previous date value.

Date	A	Variance A	B	Variance B	C	Variance C
16-10-23	132	0	228	0	46	0
17-10-23	110	-22	221	-7	241	195
18-10-23	228	118	65	-156	142	-99

I could achieve generation of column A, B and C. How do I bring the variance column values to the table visual?

Regards,

Harshad

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.