topic Re: Calc not resulting in Percentage?! in DAX Commands and Tips

Calc not resulting in Percentage?!

unknown917 — Tue, 26 May 2026 13:27:52 GMT

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())

Re: Calc not resulting in Percentage?!

Juan-Power-bi — Tue, 26 May 2026 14:36:01 GMT

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.

Re: Calc not resulting in Percentage?!

unknown917 — Tue, 26 May 2026 15:08:31 GMT

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

Re: Calc not resulting in Percentage?!

mizan2390 — Tue, 26 May 2026 18:54:22 GMT

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

Re: Calc not resulting in Percentage?!

unknown917 — Wed, 27 May 2026 09:13:13 GMT

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!