Row variance calculation

Question

Hi,&nbsp;We have a table and we hope to be able to calcuate row. variance.&nbsp;&nbsp;Example&nbsp;Dec 20 Variance between 15/12 (480) and 01/12 (432) is (480-432)/432 = 11%&nbsp;DateRefRevDec-20Jan-21Feb-21Mar-21Apr-21May-21Jun-21&nbsp;Var DecVar JanVar FebVar MarVar AprVar MayVar Jun01/12/202065043281612721872232827883006&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;15/12/202073048081613202040254429823220&nbsp;11%&nbsp;4%9%9%7%7%18/12/2020760&nbsp;81613202040254429823220&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;02/01/2021770&nbsp;81613202040254429823220&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;12/01/2021850&nbsp;3369121680225628023096&nbsp;&nbsp;-59%-31%-18%-11%-6%-4%26/01/2021890&nbsp;3369361752230428023142&nbsp;&nbsp;&nbsp;3%4%2%&nbsp;1%01/02/2021900&nbsp;&nbsp;9361752230428283142&nbsp;&nbsp;-100%&nbsp;&nbsp;&nbsp;1%&nbsp;15/02/2021990&nbsp;&nbsp;10321824234028343123&nbsp;&nbsp;&nbsp;10%4%2%0%-1%26/02/20211050&nbsp;&nbsp;&nbsp;1896249629563264&nbsp;&nbsp;&nbsp;&nbsp;4%7%4%5%01/03/20211060&nbsp;&nbsp;&nbsp;1944259230523360&nbsp;&nbsp;&nbsp;&nbsp;3%4%3%3%15/03/20211140&nbsp;&nbsp;&nbsp;1800245230323390&nbsp;&nbsp;&nbsp;&nbsp;-7%-5%-1%1%30/03/20211220&nbsp;&nbsp;&nbsp;1992254430443510&nbsp;&nbsp;&nbsp;&nbsp;11%4%0%4%&nbsp;Thanks everyone can help with it

v-jingzhang · Answer

Hi&nbsp;UKNSI-Powerbi&nbsp;
&nbsp;
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.&nbsp;
Var Dec = 
var _date = MAXX(FILTER('Table','Table'[Date]&lt;EARLIER('Table'[Date])),'Table'[Date])
var _value = MAXX(FILTER('Table','Table'[Date]=_date),'Table'[Dec-20])
return
DIVIDE('Table'[Dec-20]-_value,_value)

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

uknsi-powerbi · Answer

Hi&nbsp;v-jingzhang&nbsp;&nbsp;It seems my post need further details.&nbsp;At the moment I have a table of customer cumulations grouped by&nbsp; Cust/Seq/Contract/Part/Date/Release/YYYYMM&nbsp;CustSeqContractPartDateReleaseRevYYYYMMCUMULATIONCUST1&nbsp;&nbsp;&nbsp;&nbsp;10035987PART1&nbsp;&nbsp;&nbsp;&nbsp;01/12/2020650202012432CUST1&nbsp;&nbsp;&nbsp;&nbsp;10035987PART1&nbsp;&nbsp;&nbsp;&nbsp;02/12/2020660202012432CUST1&nbsp;&nbsp;&nbsp;&nbsp;10035987PART1&nbsp;&nbsp;&nbsp;&nbsp;03/12/2020670202012424CUST1&nbsp;&nbsp;&nbsp;&nbsp;10035987PART1&nbsp;&nbsp;&nbsp;&nbsp;07/12/2020680202012424CUST1&nbsp;&nbsp;&nbsp;&nbsp;10035987PART1&nbsp;&nbsp;&nbsp;&nbsp;08/12/2020690202012432CUST1&nbsp;&nbsp;&nbsp;&nbsp;10035987PART1&nbsp;&nbsp;&nbsp;&nbsp;09/12/2020700202012480CUST1&nbsp;&nbsp;&nbsp;&nbsp;10035987PART1&nbsp;&nbsp;&nbsp;&nbsp;10/12/2020710202012480CUST1&nbsp;&nbsp;&nbsp;&nbsp;10035987PART1&nbsp;&nbsp;&nbsp;&nbsp;15/12/2020720202012480CUST1&nbsp;&nbsp;&nbsp;&nbsp;10035987PART1&nbsp;&nbsp;&nbsp;&nbsp;16/12/2020730202012480CUST1&nbsp;&nbsp;&nbsp;&nbsp;10035987PART1&nbsp;&nbsp;&nbsp;&nbsp;01/12/2020650202101816CUST1&nbsp;&nbsp;&nbsp;&nbsp;10035987PART1&nbsp;&nbsp;&nbsp;&nbsp;02/12/2020660202101816CUST1&nbsp;&nbsp;&nbsp;&nbsp;10035987PART1&nbsp;&nbsp;&nbsp;&nbsp;03/12/2020670202101808CUST1&nbsp;&nbsp;&nbsp;&nbsp;10035987PART1&nbsp;&nbsp;&nbsp;&nbsp;07/12/2020680202101808CUST1&nbsp;&nbsp;&nbsp;&nbsp;10035987PART1&nbsp;&nbsp;&nbsp;&nbsp;08/12/2020690202101816CUST1&nbsp;&nbsp;&nbsp;&nbsp;10035987PART1&nbsp;&nbsp;&nbsp;&nbsp;09/12/2020700202101888CUST1&nbsp;&nbsp;&nbsp;&nbsp;10035987PART1&nbsp;&nbsp;&nbsp;&nbsp;10/12/2020710202101888CUST1&nbsp;&nbsp;&nbsp;&nbsp;10035987PART1&nbsp;&nbsp;&nbsp;&nbsp;15/12/2020720202101888CUST1&nbsp;&nbsp;&nbsp;&nbsp;10035987PART1&nbsp;&nbsp;&nbsp;&nbsp;16/12/2020730202101816CUST1&nbsp;&nbsp;&nbsp;&nbsp;10035987PART1&nbsp;&nbsp;&nbsp;&nbsp;17/12/2020740202101816CUST1&nbsp;&nbsp;&nbsp;&nbsp;10035987PART1&nbsp;&nbsp;&nbsp;&nbsp;17/12/2020750202101816&nbsp;On PBI the matrix is looking like this&nbsp;&nbsp;I would like to be able to calculate the variance in PBI for the same group&nbsp;Cust/Seq/Contract/Part/Date/Release/YYYYMM day by day.&nbsp;Example&nbsp;CustSeqContractPartDateReleaseRevYYYYMMCUMULATIONVarianceCUST1&nbsp;&nbsp;&nbsp;&nbsp;10035987PART1&nbsp;&nbsp;&nbsp;&nbsp;01/12/2020650202012432&nbsp;CUST1&nbsp;&nbsp;&nbsp;&nbsp;10035987PART1&nbsp;&nbsp;&nbsp;&nbsp;02/12/20206602020124320%CUST1&nbsp;&nbsp;&nbsp;&nbsp;10035987PART1&nbsp;&nbsp;&nbsp;&nbsp;03/12/2020670202012424-2%CUST1&nbsp;&nbsp;&nbsp;&nbsp;10035987PART1&nbsp;&nbsp;&nbsp;&nbsp;07/12/20206802020124240%CUST1&nbsp;&nbsp;&nbsp;&nbsp;10035987PART1&nbsp;&nbsp;&nbsp;&nbsp;08/12/20206902020124322%CUST1&nbsp;&nbsp;&nbsp;&nbsp;10035987PART1&nbsp;&nbsp;&nbsp;&nbsp;09/12/202070020201248011%CUST1&nbsp;&nbsp;&nbsp;&nbsp;10035987PART1&nbsp;&nbsp;&nbsp;&nbsp;10/12/20207102020124800%CUST1&nbsp;&nbsp;&nbsp;&nbsp;10035987PART1&nbsp;&nbsp;&nbsp;&nbsp;15/12/20207202020124800%CUST1&nbsp;&nbsp;&nbsp;&nbsp;10035987PART1&nbsp;&nbsp;&nbsp;&nbsp;16/12/20207302020124800%CUST1&nbsp;&nbsp;&nbsp;&nbsp;10035987PART1&nbsp;&nbsp;&nbsp;&nbsp;01/12/202065020210181670%CUST1&nbsp;&nbsp;&nbsp;&nbsp;10035987PART1&nbsp;&nbsp;&nbsp;&nbsp;02/12/20206602021018160%CUST1&nbsp;&nbsp;&nbsp;&nbsp;10035987PART1&nbsp;&nbsp;&nbsp;&nbsp;03/12/2020670202101808-1%CUST1&nbsp;&nbsp;&nbsp;&nbsp;10035987PART1&nbsp;&nbsp;&nbsp;&nbsp;07/12/20206802021018080%CUST1&nbsp;&nbsp;&nbsp;&nbsp;10035987PART1&nbsp;&nbsp;&nbsp;&nbsp;08/12/20206902021018161%CUST1&nbsp;&nbsp;&nbsp;&nbsp;10035987PART1&nbsp;&nbsp;&nbsp;&nbsp;09/12/20207002021018889%CUST1&nbsp;&nbsp;&nbsp;&nbsp;10035987PART1&nbsp;&nbsp;&nbsp;&nbsp;10/12/20207102021018880%CUST1&nbsp;&nbsp;&nbsp;&nbsp;10035987PART1&nbsp;&nbsp;&nbsp;&nbsp;15/12/20207202021018880%CUST1&nbsp;&nbsp;&nbsp;&nbsp;10035987PART1&nbsp;&nbsp;&nbsp;&nbsp;16/12/2020730202101816-8%CUST1&nbsp;&nbsp;&nbsp;&nbsp;10035987PART1&nbsp;&nbsp;&nbsp;&nbsp;17/12/20207402021018160%CUST1&nbsp;&nbsp;&nbsp;&nbsp;10035987PART1&nbsp;&nbsp;&nbsp;&nbsp;17/12/20207502021018160%&nbsp;Your help will be really appreciate.Thanks&nbsp;Joe

Forum Discussion

Row variance calculation

2 Replies

Related content

Variance calculation dex

Calculate average variance

Dynamic Format DAX measure calculating variance

Row wise calculation

DAX Measure for Ranking the valid rows and calculating the average rating

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%

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