Forum Discussion

Curtw01's avatar
Curtw01
Frequent Visitor
3 years ago
Solved

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 displ...
  • tamerj1's avatar
    tamerj1
    3 years ago

    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 )
    )

  • tamerj1's avatar
    tamerj1
    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 using

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

    Dense
    )

tamerj1's avatar
tamerj1
Community Champion
3 years ago

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 )
)

Curtw01's avatar
Curtw01
Frequent Visitor
3 years ago

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? 

  • tamerj1's avatar
    tamerj1
    Community Champion
    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 using

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

    Dense
    )

    • Curtw01's avatar
      Curtw01
      Frequent Visitor
      3 years ago

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