Monthly Goal Trending

[sanjay_akut]

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.

[SamLester]

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)

[sanjay_akut]

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"?

[SamLester]

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)