Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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 using
RANKX (
'Date',
'Date'[YearMonth],, -- In the form of YYYYMM like 202307
ASC,
Dense
)
Hi @Curtw01
is the month placed in the columns of the matrix from a date table? Do you have a YearMonth rank column?
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 )
)
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?
@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
)
Ahh yes that now makes sense, appreciate your help once again!
Had to tweak slightly but that worked a treat! Thank you very much!
User | Count |
---|---|
50 | |
23 | |
18 | |
18 | |
14 |
User | Count |
---|---|
91 | |
85 | |
46 | |
28 | |
21 |