Yearly Goals allocated down to days/months AND person selected

[anktaggrwl]

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 ID	Goal	FY Start	FY End
1	10	1-Oct-15	30-Sep-16
1	15	1-Oct-16	30-Sep-17
2	10	1-Oct-15	30-Sep-16
2	15	1-Oct-16	30-Sep-17
3	10	1-Oct-15	30-Sep-16
3	15	1-Oct-16	30-Sep-17
3	20	1-Oct-17	30-Sep-18
4	10	1-Oct-15	30-Sep-16
4	15	1-Oct-16	30-Sep-17
5	10	1-Oct-14	30-Sep-15
5	15	1-Oct-15	30-Sep-16
5	20	1-Oct-16	30-Sep-17
5	30	1-Oct-17	30-Sep-18

My Meetings table is like so:

Table2

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

and my attendees table is like this:

Table3

Meeting  IDAssociateID

A	1
A	3
B	5
C	2
C	3
C	4
D	1
D	2
D	3
D	5

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: 

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:

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!

[v-xjiin-msft]

@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]

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