Aggregation based on a field from other table with no direct link and different level of associations

[qess17]

**Issue Description:**

I am encountering a problem with linking data between tables in my data model.

**Background:**

- **Data Model Structure:** The data model is designed using a star schema.
- **Tables Involved:**
- **Master Calendar Table:** Contains date information. The first two columns are from this table.
- **Payroll Table:** Contains payroll data. The remaining columns are from this table.
- **Linktable:** Serves as a bridge between the Master Calendar and Payroll tables since there is no direct connection between them.
- **Existing Relationships:**
- The **Master Calendar** and **Linktable** are connected via the `userdate` field, representing daily dates over a 5-year period.
- The **Payroll** and **Linktable** are connected through a composite key consisting of `[Company Name]` and `[Transaction Year]`.

**Problem Details:**

- As shown in the screenshot (not included here), for each month in the **Master Calendar** table, there are at least eight corresponding entries in the **Payroll** table.
- The issue arises because the `Month` field from the **Master Calendar** table is effectively acting as the `Year` field in the **Payroll** table.
- I need to align the monthly calculation values from the **Payroll** table with the months in the **Master Calendar** table.
- In essence, I want to link the two tables at the **month level**, rather than the current `Month=Year` association.

**Constraints:**

- I cannot redesign or alter the existing data model because the current relationships are essential for other calculations.

**Request for Assistance:**

- How can I correlate the monthly data between the **Payroll** and **Master Calendar** tables at the month level without modifying the existing data model?
- Are there any methods or workarounds that allow for this month-level linkage within the current constraints?

**Additional Information:**

- I am looking for a solution that maintains the integrity of the current data model while allowing for accurate monthly calculations.
- Any suggestions or guidance on resolving this issue would be greatly appreciated.

---

**Thank you in advance for your support!**

[henrikalmen]

Could you give an example of what your expected result is? I'm not fully understanding what it is you want to achieve.

[marcus_sommer]

What you are describing is not a star-scheme data-model which has just a fact-table with n dimension-tables. I suggest to re-design the data-model to it because it's the simplest approach and worked within the most scenarios very well.