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 🙂

1 ACCEPTED SOLUTION
Super User

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!

 Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Super User

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!

 Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.