cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

Ticket remaning to goal by quarter

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 🙂

Community Champion

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)

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