Forum Discussion

sanjay_akut's avatar
sanjay_akut
Regular Visitor
10 years ago

Monthly Goal Trending

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 w...
sanjay_akut's avatar
sanjay_akut
Regular Visitor
10 years ago

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's avatar
SamLester
Microsoft Employee
10 years ago

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)