Forum Discussion
Calculating difference between two column values in matrix
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
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 )
)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 usingRANKX (
'Date',
'Date'[YearMonth],, -- In the form of YYYYMM like 202307
ASC,Dense
)
7 Replies
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.
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 )
)
