%Var between previous date

[nzboan]

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 😄

[Ashish_Mathur]

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.

[Anonymous]

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