How to connect changing monthly targets with production data?

[AJTK]

Hi everyone, 

I have 3 programs, with 4 different regions. The target changes every month for each program & region. For example, as below: 

Program	Region	Target Month	Target
A	North	1/1/2024	1000
A	South	1/1/2024	1000
A	East	1/1/2024	1000
A	West	1/1/2024	900
B	North	1/1/2024	500
B	South	1/1/2024	500
B	East	1/1/2024	500
B	West	1/1/2024	200
C	North	1/1/2024	500
C	South	1/1/2024	500
C	East	1/1/2024	500
C	West	1/1/2024	200
A	North	2/1/2024	1500
A	South	2/1/2024	1500
A	East	2/1/2024	1500
A	West	2/1/2024	1250
B	North	2/1/2024	1000
B	South	2/1/2024	1000
B	East	2/1/2024	500
B	West	2/1/2024	500
C	North	2/1/2024	800
C	South	2/1/2024	800
C	East	2/1/2024	500
C	West	2/1/2024	350

How do I link this target table to my production data, and link to the correct program and region? If I make a unique code for each program & region to link between the two tables (for example, A-North, C-West, etc.), how can I have it take into account the month of the target & month of production to show whether we are meeting our monthly targets for each program and region? Thanks!

[AJTK]

Thanks! That was really helpful. Appreciate your effort and time!

[gmsamborn · Accepted solution]

Hi @AJTK 

Don't try to link two fact tables (Target, Production).

Instead, I would create dimension tables for Program and Region either by using "Enter Data" or Power Query.  (Usually that data is already available to you.)

I would also create a Date dimension table.

The dimension tables should be related to the fact tables using 1-to-many (1:*) relationships with the dimension table being on the "one side".

After that and a couple of simple measures, you should be able to slice/filter based on any or all of the 3 dimension tables.

Simple Star Schema.pbix

I hope this helps.