Calc not resulting in Percentage?!

[unknown917]

I have the following calculated column which should be resulting in a % of change, but is returning whole numbers.  I broke down each step of the calc and each VAR calculates as intended except the division calc.  Any thoughts to my error?

Example - _Curr = 78,062 _Prev = 128,777 _Diff = 50,715
                 _Vs = .649 *intended*  -1298 is *actual*

Column = VAR  _Cases = CALCULATE(SELECTEDVALUE('Table'[Inv Qty]))

VAR _Curr = CALCULATE(

            SUMX('Table',_Cases),

            (DATESBETWEEN(Calendar_Query[Date],

            EOMONTH(MAX(Calendar_Query[Date]),-12)+1,

            EOMONTH(MAX(Calendar_Query[Date]),0))))

VAR _Prev = CALCULATE(

            SUMX('Table',_Cases),

            (DATESBETWEEN(Calendar_Query[Date],

            MIN(Calendar_Query[Date]),

            EDATE(MIN(Calendar_Query[Date]),12))))

VAR _Diff = CALCULATE(_Curr - _Prev)

RETURN

VAR _Vs = _Diff/_Prev

RETURN

IFERROR(_Vs,BLANK())

[mizan2390 · Accepted solution]

hi @unknown917 

To do dynamic time intelligence, this must be a Measure, not a Calculated Column. Furthermore, we need to abandon SUMX and rely on standard aggregation combined with proper DATESINPERIOD modifiers.

Here is the DAX measure. It calculates a Trailing 12 Month (TTM) vs. the Prior Trailing 12 Month (Prior TTM):

% Change (TTM) =

// 1. Establish the base aggregation (No SUMX needed)

VAR BaseQty = SUM('Table'[Inv Qty])

// 2. Calculate the Current 12 Months

VAR _Curr =

CALCULATE(

BaseQty,

DATESINPERIOD(

Calendar_Query[Date],

MAX(Calendar_Query[Date]),

-12,

MONTH

)

)

// 3. Calculate the Previous 12 Months (Shifting the current period back 1 year)

VAR _Prev =

CALCULATE(

BaseQty,

DATEADD(

DATESINPERIOD(

Calendar_Query[Date],

MAX(Calendar_Query[Date]),

-12,

MONTH

),

-1,

YEAR

)

)

// 4. Calculate the Variance

VAR _Diff = _Curr - _Prev

// 5. Safely divide, returning BLANK if dividing by zero

RETURN

DIVIDE(_Diff, _Prev, BLANK())

if this solves your problem, please mark this as solution and give me a kudos. 
@me so that i dont loose this thread

[unknown917]

@mizan2390 - Thank you for your help!  I found that the VAR for baseqty was not calculating, so I shifted that into the Curr & Prev Vars.  Works and does not result in the memory error.

_Prev = CALCULATE(

    SUM('Table'[Inv Qty]),

    DATEADD(

    DATESINPERIOD(Calendar_Query[Date],

    MAX(Calendar_Query[Date]),

    -12,

    MONTH),

    -1,

    YEAR)

    )

Thank you!

[Juan-Power-bi]

Hello,

First, the math is off.

You're dividing _Diff / _Prev but _Diff = _Curr - _Prev, so with your numbers that's (78,062 - 128,777) / 128,777 = -0.39, not 0.649. If you want the change relative to the previous period, the formula is right , but the expected result you wrote (.649) doesn't match those numbers. 

on the other hnd, and more importantly, this is a calculated column, but you're using CALCULATE with DATESBETWEEN on a calendar table. In a calculated column context, MAX(Calendar_Query[Date]) and MIN(Calendar_Query[Date]) are going to return the max/min of the entire calendar table, not anything scoped to the current row. That's almost certainly why the numbers are wrong. 

Also, wrapping _Curr - _Prev inside another CALCULATE for _Diff does nothing , you can just write VAR _Diff = _Curr - _Prev directly.

[unknown917]

Any suggestions?  I initially used a measure(s) to calc this out, but I'm getting "Query has exceeded the available resources."  message