Ticket remaning to goal by quarter

[kimdiep503]

I'm having trouble with coming up with a measure to calculate the number of open & closed VER (aka tickets) to goal each quarter shown in the two boxes below. Currently I have to manually input the calculation, so for example, for the "FY24Q3 opened VERs Remaining to goal", I take (9-160) to get -151. If the number of tickets surpass last quarter's number, then I would want the value to be zero. The same applies to the "FY24Q3 Closed VERs remaining to Goal". Greatly appreciate any tips or recommendations 🙂

[rajendraongole1 · Accepted solution]

Hi @kimdiep503 -  create two measures that dynamically calculate these values based on the current data as below

Measure one for OPen

Remaining Open VERs to Goal =
VAR CurrentQuarter = SELECTEDVALUE('VERs'[Quarter])
VAR OpenGoal = LOOKUPVALUE(Goals[OpenGoal], Goals[Quarter], CurrentQuarter)
VAR OpenedVERs = CALCULATE(COUNTROWS('VERs'), 'VERs'[Status] = "Open")
VAR Remaining = OpenGoal - OpenedVERs
RETURN IF(Remaining < 0, 0, Remaining)

Measure two for closed:

Remaining Closed VERs to Goal =
VAR CurrentQuarter = SELECTEDVALUE('VERs'[Quarter])
VAR ClosedGoal = LOOKUPVALUE(Goals[ClosedGoal], Goals[Quarter], CurrentQuarter)
VAR ClosedVERs = CALCULATE(COUNTROWS('VERs'), 'VERs'[Status] = "Closed")
VAR Remaining = ClosedGoal - ClosedVERs
RETURN IF(Remaining < 0, 0, Remaining)

It works. please check

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!