Forum Discussion

nzboan's avatar
nzboan
New Member
8 years ago

%Var between previous date

Hey, everyone, I've been trying to calculate this, but I'm really stuck. Here is the problem:

 

I want to show in % the difference between months of 2016 and 2017. For example :

 

January 2016: 10

January 2017: 11

% diff: +10%

 

Thanks in advance :D

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi nzboan,

     

    You can use below formula to calculate the diff between current year month total and previous year month total.

    Measure:

    Diff = 
VAR CYM =
    CALCULATE (
        SUM ( Records[Amount] ),
        FILTER (
            ALLSELECTED ( Records ),
            YEAR ( [Date] ) = YEAR ( MAX ( [Date] ) )
                && MONTH ( [Date] ) = MONTH ( MAX ( [Date] ) )
        )
    )
VAR PYM =
    CALCULATE (
        SUM ( Records[Amount] ),
        FILTER (
            ALLSELECTED ( Records ),
            YEAR ( [Date] )
                = YEAR ( MAX ( [Date] ) ) - 1
                && MONTH ( [Date] ) = MONTH ( MAX ( [Date] ) )
        )
    )
RETURN
    DIVIDE ( CYM - PYM, PYM, 0 )

     

    Regards,

    Xiaoxin Sheng

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    If the following conditions are met

     

    1. You have a calendar table
    2. There is a relationship from the Date column of your data table to the date column of your calendar table
    3. You extract year and month in the calendar table
    4. You drag Year and Month from the calendar table into the visual

     

    then, this measure will work

     

    =SUM(Data[Value])/CALCULATE(Data[Value]),PREVIOUSMONTH(Calendar[Date]))

    Hope this helps.