Row variance calculation

[UKNSI-Powerbi]

Hi,

We have a table and we hope to be able to calcuate row. variance. 

Example 

Dec 20 Variance between 15/12 (480) and 01/12 (432) is (480-432)/432 = 11%

Date	Ref	Rev	Dec-20	Jan-21	Feb-21	Mar-21	Apr-21	May-21	Jun-21		Var Dec	Var Jan	Var Feb	Var Mar	Var Apr	Var May	Var Jun
01/12/2020	65	0	432	816	1272	1872	2328	2788	3006
15/12/2020	73	0	480	816	1320	2040	2544	2982	3220		11%		4%	9%	9%	7%	7%
18/12/2020	76	0		816	1320	2040	2544	2982	3220
02/01/2021	77	0		816	1320	2040	2544	2982	3220
12/01/2021	85	0		336	912	1680	2256	2802	3096			-59%	-31%	-18%	-11%	-6%	-4%
26/01/2021	89	0		336	936	1752	2304	2802	3142				3%	4%	2%		1%
01/02/2021	90	0			936	1752	2304	2828	3142			-100%				1%
15/02/2021	99	0			1032	1824	2340	2834	3123				10%	4%	2%	0%	-1%
26/02/2021	105	0				1896	2496	2956	3264					4%	7%	4%	5%
01/03/2021	106	0				1944	2592	3052	3360					3%	4%	3%	3%
15/03/2021	114	0				1800	2452	3032	3390					-7%	-5%	-1%	1%
30/03/2021	122	0				1992	2544	3044	3510					11%	4%	0%	4%

Thanks everyone can help with it

[UKNSI-Powerbi]

Hi @v-jingzhang 

It seems my post need further details.

At the moment I have a table of customer cumulations grouped by  Cust/Seq/Contract/Part/Date/Release/YYYYMM

Cust	Seq	Contract	Part	Date	Release	Rev	YYYYMM	CUMULATION
CUST1	1	0035987	PART1	01/12/2020	65	0	202012	432
CUST1	1	0035987	PART1	02/12/2020	66	0	202012	432
CUST1	1	0035987	PART1	03/12/2020	67	0	202012	424
CUST1	1	0035987	PART1	07/12/2020	68	0	202012	424
CUST1	1	0035987	PART1	08/12/2020	69	0	202012	432
CUST1	1	0035987	PART1	09/12/2020	70	0	202012	480
CUST1	1	0035987	PART1	10/12/2020	71	0	202012	480
CUST1	1	0035987	PART1	15/12/2020	72	0	202012	480
CUST1	1	0035987	PART1	16/12/2020	73	0	202012	480
CUST1	1	0035987	PART1	01/12/2020	65	0	202101	816
CUST1	1	0035987	PART1	02/12/2020	66	0	202101	816
CUST1	1	0035987	PART1	03/12/2020	67	0	202101	808
CUST1	1	0035987	PART1	07/12/2020	68	0	202101	808
CUST1	1	0035987	PART1	08/12/2020	69	0	202101	816
CUST1	1	0035987	PART1	09/12/2020	70	0	202101	888
CUST1	1	0035987	PART1	10/12/2020	71	0	202101	888
CUST1	1	0035987	PART1	15/12/2020	72	0	202101	888
CUST1	1	0035987	PART1	16/12/2020	73	0	202101	816
CUST1	1	0035987	PART1	17/12/2020	74	0	202101	816
CUST1	1	0035987	PART1	17/12/2020	75	0	202101	816

On PBI the matrix is looking like this

I would like to be able to calculate the variance in PBI for the same group Cust/Seq/Contract/Part/Date/Release/YYYYMM day by day.

Example 

Cust	Seq	Contract	Part	Date	Release	Rev	YYYYMM	CUMULATION	Variance
CUST1	1	0035987	PART1	01/12/2020	65	0	202012	432
CUST1	1	0035987	PART1	02/12/2020	66	0	202012	432	0%
CUST1	1	0035987	PART1	03/12/2020	67	0	202012	424	-2%
CUST1	1	0035987	PART1	07/12/2020	68	0	202012	424	0%
CUST1	1	0035987	PART1	08/12/2020	69	0	202012	432	2%
CUST1	1	0035987	PART1	09/12/2020	70	0	202012	480	11%
CUST1	1	0035987	PART1	10/12/2020	71	0	202012	480	0%
CUST1	1	0035987	PART1	15/12/2020	72	0	202012	480	0%
CUST1	1	0035987	PART1	16/12/2020	73	0	202012	480	0%
CUST1	1	0035987	PART1	01/12/2020	65	0	202101	816	70%
CUST1	1	0035987	PART1	02/12/2020	66	0	202101	816	0%
CUST1	1	0035987	PART1	03/12/2020	67	0	202101	808	-1%
CUST1	1	0035987	PART1	07/12/2020	68	0	202101	808	0%
CUST1	1	0035987	PART1	08/12/2020	69	0	202101	816	1%
CUST1	1	0035987	PART1	09/12/2020	70	0	202101	888	9%
CUST1	1	0035987	PART1	10/12/2020	71	0	202101	888	0%
CUST1	1	0035987	PART1	15/12/2020	72	0	202101	888	0%
CUST1	1	0035987	PART1	16/12/2020	73	0	202101	816	-8%
CUST1	1	0035987	PART1	17/12/2020	74	0	202101	816	0%
CUST1	1	0035987	PART1	17/12/2020	75	0	202101	816	0%

Your help will be really appreciate.

Thanks 

Joe

[v-jingzhang]

Hi @UKNSI-Powerbi 

Does your current data table have the format as you have shown? If so, you can add new columns to the table with below DAX. You just need to modify the column name and the value column it references. 

Var Dec = 
var _date = MAXX(FILTER('Table','Table'[Date]<EARLIER('Table'[Date])),'Table'[Date])
var _value = MAXX(FILTER('Table','Table'[Date]=_date),'Table'[Dec-20])
return
DIVIDE('Table'[Dec-20]-_value,_value)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!