Skip to main content
cancel
Showing results for 
Search instead 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

Reply
kimdiep503
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 🙂

 

kimdiep503_0-1720531442173.png

 

1 REPLY 1
rajendraongole1
Community Champion
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)

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.