Forum Discussion
Calculating difference between two column values in matrix
- 3 years ago
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 )
) - 3 years ago
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
)
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 )
)