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
    )