cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
anktaggrwl Regular Visitor
Regular Visitor

Yearly Goals allocated down to days/months AND person selected

Our team has a yearly meetings goal, and I'd like to track each individual member's progress toward their personal goal over time, and have the goal line displayed on a bar chart that changes according to the date hierarchy the user drills down on.

 

My goals table is currently laid out like so:

Table1

Associate IDGoalFY StartFY End
1101-Oct-1530-Sep-16
1151-Oct-1630-Sep-17
2101-Oct-1530-Sep-16
2151-Oct-1630-Sep-17
3101-Oct-1530-Sep-16
3151-Oct-1630-Sep-17
3201-Oct-1730-Sep-18
4101-Oct-1530-Sep-16
4151-Oct-1630-Sep-17
5101-Oct-1430-Sep-15
5151-Oct-1530-Sep-16
5201-Oct-1630-Sep-17
5301-Oct-1730-Sep-18

 

My Meetings table is like so:

Table2

 

Meeting DateMeeting ID
11-Oct-17A
08-Jan-18B
03-Jan-17C
21-Dec-17D
19-May-18E

 

and my attendees table is like this:

Table3

Meeting  IDAssociateID

A1
A3
B5
C2
C3
C4
D1
D2
D3
D5


I have a Date table connected to my meetings table, and am using time intelligence to chart out meetings by either Year, Month, or even Day depending on how the user wants to track their own progress: 

 

Capture.PNG

 

I've written a measure that will give me the Yearly goal as a number:

 

Meeting Goal =
    CALCULATE (
        SUM ( Table1[Goal] ),
        FILTER ( 'Table1', Table1[FYStart] >= MIN ( 'Date'[Date] ) ),
        TREATAS ( VALUES ( Table3[AssociateID] ), Table1[AssociateID] )
    )

 

and when I try to chart that goal, obviously it doesn't take the yearly goal and spread it out over the number of months/days shown, what I get is:

Capture2.PNG

 

 

 

The idea is that for the selected Associate ID - I not only want the correct yearly goal to display - but I also want it to be spread out evenly by the number of days between the FY start and FY end dates. The measure above gives me the correct goal number for each associate - but instead of just 1 dot - i want a cumulative goal line to show how someone is tracking over the course of the year...

Let's pretend the green bars in the above are cumulative meeting counts for Associate ID 5 (they're not cumulative in the measure I wrote yet, but I can do that, just havent gotten to it yet) but if I wanted to show cumulative meetings from 1-Oct-2017 and then a goal line that starts at zero and ends at 30 on 30-Sep-2018 for Associate ID 5, but then have it start at 0 and end at the yearly goal for Associate 4 if that's who is selected... how do I do that?

 

I've tried dividing the yearly goal by the number of days in a year to come to a daily goal, and have thought about creating a second date table with every date and allocating the daily goal to each date and then calculating a running todal - but if I go down that route - I don't know how I would account for over 100 different individual associates yearly goals...

 

help!

 

2 REPLIES 2
v-xjiin-msft Super Contributor
Super Contributor

Re: Yearly Goals allocated down to days/months AND person selected

@anktaggrwl

 

Since your report is pretty complicated. Could you please share us your report or a sample report with One Drive or Dropbox or something if possible? If will help us understand your requirement more easily.

 

Thanks,
Xi Jin.

anktaggrwl Regular Visitor
Regular Visitor

Re: Yearly Goals allocated down to days/months AND person selected

It's taken me a while to come back to this - simply because we ended up not needing to focus on the issue again until now, it's come up again.

 

I've included a sample file I put together - and I'm looking to learn how to answer the following:

 

Manager A is has a yearly meeting goal of 15 meetings in 2018, and assuming he is looking to shoot for 15 meetings by the end of the year, is his current meeting progress on pace for him to meet his goal? i.e. on April 11th, is he above or below pace to hit his goal of 15 meetings at the end of the year?

 

 

What I'd ideally like to do is show YTD actual meeting count versus where he should be if we assume he will have 0 meetings on Jan 1, and will end at 15 meetings on Dec 31 2018 - so if his YTD figure is above the line, he knows his pace is on track to meet his goal, and if he is below the line, it means he needs to book more meetings to close the gap.

 

Can anyone help?

 

Sample pbix for goal pacing

 

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 46 members 845 guests
Please welcome our newest community members: