topic Re: %Var between previous date in Desktop

%Var between previous date

nzboan — Wed, 11 Oct 2017 23:08:55 GMT

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

Re: %Var between previous date

Anonymous — Fri, 13 Oct 2017 02:18:35 GMT

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

Re: %Var between previous date

Ashish_Mathur — Fri, 13 Oct 2017 03:20:39 GMT

Hi,

If the following conditions are met

You have a calendar table
There is a relationship from the Date column of your data table to the date column of your calendar table
You extract year and month in the calendar table
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.