Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Hanshans
Helper I
Helper I

Adjusting Monthly Targets based on Previous Month Performance and Moving Total

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.

Hanshans_0-1695207353992.png

 

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

Hanshans_1-1695207427877.png

 

'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

Hanshans_2-1695207742832.png

 

'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

 

Hanshans_3-1695207989513.png

 

Is this possible? Thank you in advance

 

 

1 REPLY 1
v-rzhou-msft
Community Support
Community Support

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.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.