Forum Discussion
Adding targets with calculated Fiscal date
Problem: Adding target data to tables, charts, and other visuals
I have 2 tables with information that I would like to combine.
Table 1:
| Project ID request | Geo | Date | Fiscal Quarter and Year |
| 1234 | EMEA | 1 june 2023 | Q2 - FY23 |
| 1235 | EMEA | 6 june 2023 | Q3 - FY23 |
The date in this table is recognized as a date column, but as we use a different fiscal timetable, I have made a DAX function to calculate the Fiscal Quarter and Year. This is now just a text file.
Table 2
| Geo | Fiscal Quarter and Year | Target |
| EMEA | Q1 - FY23 | 50 |
| EMEA | Q2 - FY23 | 60 |
| EMEA | Q3 - FY23 | 70 |
| AMER | Q1 - FY23 | 60 |
The second table holds the information for the targets. This is just an Excel file with static information, with all 3 Geo (so US and APAC are also in there, but I have not added them in this example).
The outcome would need to be somehting like this:
| Geo | Fiscal quarter and year | Number of projects | Target | Attainment % |
| EMEA | Q1 - FY23 | 100 | 50 | 200% |
| AMER | Q1 - FY23 | 80 | 60 | 133% |
| APAC | Q1 - FY23 | 20 | 40 | 50% |
Can someone please point me in the right direction, because when I combine the 2 tables, it will not show the end result as I expect.
1 Reply
The right direction is to add a calendar table to your data model. Maintain the Fiscal calendar details in that table instead of your fact tables.
