I have two Dim tables. One for Budgets and the other for Transactions.
Part of the budget is the work-related local travel budget and each organization has their own. Example would be a mechanic is sent from the East side of the city to the South and it cost $120 in fuel, etc.
Each DIm Table has the organizations ID, and there is an organization lookup table with the ID and associated Org name. Each ID is unique but an Org can have multiple ID's.
Org Lookup Table example:
| OrgID | OrgName |
| 11A | SouthTeam |
| 11B | NorthTeam |
| 12A | NorthTeam |
| 12B | EastTeam |
| 12C | SouthTeam |
The Budget table Example:
| OrgID | Budget | Type | Year |
| 11A | 5000 | Travel Repairs | 2023 |
| 12B | 1000 | Travel Fuel | 2023 |
| 12A | 1500 | Travel Repairs | 2023 |
| 11A | 1000 | Travel Fuel | 2023 |
| 12C | 4000 | Travel Repairs | 2022 |
| 12B | 500 | Travel Repairs | 2022 |
Transaction Example:
| OrgID | Spent | Type | Year |
| 12A | 5.50 | Travel Fuel | 2023 |
| 12B | 20 | Travel Repairs | 2023 |
I took two approaches, first i built the relatinship between Org ID of the lookup table to each dim table to show the budget and cost, successfully and then I tried adding a RELATED column in each DIM table.
The problem is this, when I can't add in a calendar table as there is already an active relatinship. Our fiscal year is from July 1 to June 30; right now we are in Fiscal Year 2023 and on July 1st we will be in Fiscal Year 2024. I am trying to build a slicer for the FY to filter both tables with no luck ...
