topic Monthly Goal Trending in Developer

Monthly Goal Trending

sanjay_akut — Sun, 05 Jun 2016 14:22:13 GMT

So I have a goal table containing the MONTHLY goal for a particular measure which I have stored in a goals table using the first day of the month for the month that the goal is applied to (1/1/2016 would have a goal value of 100). For the actuals, we actually capture that daily and I am trying to determine if we are "on target" or not based on the goal and the actuals MTD. I am not sure how to properly determine what the daily value of the goal should be in order to determine if our actuals are trending to be able to make that goal. Using the KPI visual it turns green only after the date where we meet the goal, but doesn't help to determine trending since my goal table only has that monthly goal.

Any help to point me in the right direction would be greatly appreciated.

Re: Monthly Goal Trending

SamLester — Sun, 05 Jun 2016 15:13:45 GMT

Could you use a Gauge control to show this instead of a KPI visual? You could set the value as the sum of the daily actuals and the maximum value to the Monthly Goal. You could also set the Target Value to be a calculation based on days in the month.

Thanks,
Sam Lester (MSFT)

Re: Monthly Goal Trending

sanjay_akut — Sat, 11 Jun 2016 13:36:10 GMT

Thanks for the suggestion, this is a good start. Any suggestions on creating the calculation using the days in a month, so if the monthly goal was 30 I could know that on the 11th day of the month, if my value is 11 I am "on target"?

Re: Monthly Goal Trending

SamLester — Sun, 12 Jun 2016 08:34:00 GMT

Sure, you can use DAX date/time calculations to determine this. Set the target value of the gauge to this calculated column to reflect the calculated goal.

Ex:

DAY(now()) / DAY(EOMONTH(NOW(),0)) * Goals[MonthlyGoal]

Today is June 12th.

DAY(now()) returns 12
DAY(EOMONTH(now(),0) returns 30 (number of days in June)

So the calculation would be:

current day / days in month * montly goal

Thanks,
Sam Lester (MSFT)