topic Re: Calculating difference between two column values in matrix in DAX Commands and Tips

Calculating difference between two column values in matrix

Curtw01 — Wed, 25 Jan 2023 15:12:34 GMT

Hi,

First ever post to the forum (I believe this is the correct place to post) so excuse me if there is too much/too little information provided.

I have a requirement whereby I need to display, on a matrix, the monthly share of orders per product over a given period. And directly below on a separate matrix, show the movement month on month for each product.

Example of what I'm trying to get to below.

No. of Orders

	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Product 1	6754	4914	4694	5071	5518	5681	6076	6739	6107	6737	5656	3966
Product 2	6845	5002	4704	5078	5432	5535	6158	6715	6039	6862	5530	3892
Product 3	6956	4843	4663	5026	5354	5463	6046	6567	6019	6972	5845	4053
Product 4	6941	4908	4575	5188	5517	5452	5943	6758	5918	6844	5764	3997
Product 5	6644	4864	4713	5101	5334	5542	6102	6694	6064	6752	5678	4123
Grand Total	34140	24531	23349	25464	27155	27673	30325	33473	30147	34167	28473	20031

Share of Orders

	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Product 1	19.78%	20.03%	20.10%	19.91%	20.32%	20.53%	20.04%	20.13%	20.26%	19.72%	19.86%	19.80%
Product 2	20.05%	20.39%	20.15%	19.94%	20.00%	20.00%	20.31%	20.06%	20.03%	20.08%	19.42%	19.43%
Product 3	20.37%	19.74%	19.97%	19.74%	19.72%	19.74%	19.94%	19.62%	19.97%	20.41%	20.53%	20.23%
Product 4	20.33%	20.01%	19.59%	20.37%	20.32%	19.70%	19.60%	20.19%	19.63%	20.03%	20.24%	19.95%
Product 5	19.46%	19.83%	20.19%	20.03%	19.64%	20.03%	20.12%	20.00%	20.11%	19.76%	19.94%	20.58%

MoM Variance

	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Product 1		0.25%	0.07%	-0.19%	0.41%	0.21%	-0.49%	0.10%	0.12%	-0.54%	0.15%	-0.07%
Product 2		0.34%	-0.24%	-0.20%	0.06%	0.00%	0.31%	-0.25%	-0.03%	0.05%	-0.66%	0.01%
Product 3		-0.63%	0.23%	-0.23%	-0.02%	0.02%	0.20%	-0.32%	0.35%	0.44%	0.12%	-0.29%
Product 4		-0.32%	-0.41%	0.78%	-0.06%	-0.62%	-0.10%	0.59%	-0.56%	0.40%	0.21%	-0.29%
Product 5		0.37%	0.36%	-0.15%	-0.39%	0.38%	0.10%	-0.12%	0.12%	-0.35%	0.18%	0.64%

Example of raw data structure below. (data below will not equal the above as its just a sample of the data)

Product	Date	Orders
Product 1	01/11/2021	1
Product 2	01/11/2021	1
Product 3	01/09/2019	1
Product 4	01/12/2021	1
Product 5	01/06/2022	1
Product 1	01/03/2019	2
Product 2	01/07/2020	1
Product 3	01/11/2019	1
Product 4	01/11/2021	1
Product 5	01/02/2021	1
Product 1	01/03/2020	1
Product 2	01/04/2021	1
Product 3	01/05/2021	1
Product 4	01/06/2020	1
Product 5	01/08/2021	2
Product 1	01/09/2020	2
Product 2	01/10/2021	1
Product 3	01/01/2019	1
Product 4	01/11/2019	2
Product 5	01/11/2019	2
Product 1	01/12/2019	1
Product 2	01/06/2020	1
Product 3	01/07/2020	1
Product 4	01/09/2022	1
Product 5	01/10/2022	1
Product 1	01/11/2022	1
Product 2	01/01/2019	1
Product 3	01/10/2020	1
Product 4	01/02/2022	1
Product 5	01/03/2022	1

I can comfortably plot the sum of orders each month for each product and also the share (%) of orders per month per product on a matrix. However, the variance month on month is where I'm having difficulty.

Can anyone advise please?

Much appreciated,

Curt

Re: Calculating difference between two column values in matrix

tamerj1 — Wed, 25 Jan 2023 15:36:16 GMT

Hi Curtw01

is the month placed in the columns of the matrix from a date table? Do you have a YearMonth rank column?

Re: Calculating difference between two column values in matrix

Curtw01 — Wed, 25 Jan 2023 16:10:13 GMT

Hi,

Yes, in my real world scenario it would be MonthYear in the columns of the matrix and I have a MonthYearNum column in my date table in which to rank.

Re: Calculating difference between two column values in matrix

tamerj1 — Wed, 25 Jan 2023 16:37:11 GMT

Curtw01

Please try

MoM Variance =
VAR CurrentValue = [No. of Orders]
VAR CuurentYearMonth =
MAX ( 'Date'[YearMonthNum] )
VAR PreviousValue =
CALCULATE (
[No. of Orders],
'Date'[YearMonthNum] = CuurentYearMonth - 1,
ALL ( 'Date' )
)
RETURN
IF (
NOT ISBLANK ( PreviousValue ),
DIVIDE ( CurrentValue - PreviousValue, CurrentValue )
)

Re: Calculating difference between two column values in matrix

Curtw01 — Thu, 26 Jan 2023 09:58:54 GMT

Had to tweak slightly but that worked a treat! Thank you very much!

Re: Calculating difference between two column values in matrix

Curtw01 — Fri, 10 Mar 2023 12:18:37 GMT

Hi,

Revisiting this and hoping you could potentially help further.

When displaying this measure in the matrix it does not seem to show any January values for any year? Any ideas how to get around this?

Re: Calculating difference between two column values in matrix

tamerj1 — Fri, 10 Mar 2023 12:28:49 GMT

Curtw01
You should be using a YearMonthNum column which is supposed to be a sequential number such as a dense rank. For example for Jan. 2023 you cannot minus one from 202301 to obtain 202212. Rather you will obtain 202300 which basically has no value in your date table. Therefore, the YearMonthNum is different than the YearMonth column which can start from 1 for example to refer to the very first month in your table and keeps adding 1 for each month after that. You can create such column using

RANKX (
'Date',
'Date'[YearMonth],, -- In the form of YYYYMM like 202307
ASC,

Dense
)

Re: Calculating difference between two column values in matrix

Curtw01 — Fri, 10 Mar 2023 13:13:23 GMT

Ahh yes that now makes sense, appreciate your help once again!