Adjusting Monthly Targets based on Previous Month Performance and Moving Total

[Hanshans]

Hi all

I have a FACT table of training attendance on different courses. Table is called 'Attendance'. For each course, there is a standard monthly target that needs to adjust by any over or under achivement the previous month.

'

For example  - training course A monthly target for September is 50. No. of people attending that course in August was 60. An extra 10 people were trained in August. So Septembers target needs to adjust to 40 (50-10)

In addition to this, the standard monthly target might chance as the total population of people who need to be trained increases. 

I have a table that contains the names and date of enrolement of everyone who needs to be trained. Table is called 'population'. This is currently used in DAX calcualations to show the total number of people who need training against number and % trained for each course. It will be this table that will change if the total population increases. If the total population of people who need training increases, then the standard monthly target for each course will need to increase. That in turn affecting the calculation of the adjustable target.

We have 7 months to train everyone.  Aug 23, Sept 23, Oct 23, Nov 23, Dec 23, Jan 24 and Feb 24. The standard monthly targets are based on  2 weeks of the month available for training in both Dec 23 and Jan 24. The full month is available for training in Aug, September, Oct, Nov and Feb.

Tables I have are

'Targets' - shows the standard monthly target per training course. Difference for each course is due to numbers already completed.

'Attendance' - who has been trained in what and when ('Project' will be required as a filter on the dashboard)

'Population' - shows total population of people who need training. Each person must attend each training course (A-D). Project will be required as a filter on the dashboard

'Calendar Table' which is the Date Table and has extensive columns including month offset from 'today' and year offset from 'today'

Desired result would be a table like below (and / or visuals) for each training course

Is this possible? Thank you in advance

[v-rzhou-msft]

Hi @Hanshans ,

I think your issue should be caused by your data model. Please share a sample file with me and show us the result you want. This will make it easier for us to find the solution.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.