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
)
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 )
)
Had to tweak slightly but that worked a treat! Thank you very much!