Ticket remaning to goal by quarter

Anonymous — Tue, 09 Jul 2024 13:30:34 GMT

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 🙂

Re: Ticket remaning to goal by quarter

rajendraongole1 — Tue, 09 Jul 2024 13:36:52 GMT

Hi Anonymous - 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!!

topic Ticket remaning to goal by quarter in DAX Commands and Tips

Ticket remaning to goal by quarter

Re: Ticket remaning to goal by quarter