A bit of an unusual one here. Basically, I have two tables of data, one of which contains hourly data spanning two years and the other contains monthly budget codes which I wish to assign to each month's sales data:
Sales Table
| DateTime | Sales Data |
| 01/01/2022 00:00:00 | Data 1 |
| 01/01/2022 00:01:00 | Data 2 |
| 01/01/2022 00:02:00 | Data 3 |
| etc. | etc. |
Monthly Budget Codes
| Date | Budget Code |
| 01/01/2022 | jan_22_budget_code |
| 01/02/2022 | feb_22_budget_code |
| etc. | etc. |
The idea is to create a Line and Cluster Column chart, using the DateTime and Sales Data from the first table for the chart portion, and the Budget Code from the second table as the Line. The underlying data would look something like this:
| DateTime | Sales Data | Budget Code |
| 01/01/2022 00:00:00 | Data 1 | jan_22_budget_code |
| 01/01/2022 00:01:00 | Data 2 | jan_22_budget_code |
| etc. | etc. | etc. |
I have tried various methods, such as creating a calculate column in the Sales table, creating a relationship between the tables, and using a common date table to no avail. I know this isn't an ideal dataset, but I'm inheriting this from someone else. Something to bear in mind is that the Budget Codes table needs to be separate, as it draws from an Excel spreadsheet, so that the finance teams can easily edit these codes, as they often change.
Thanks in advance to anyone who has taken the time to read this. I can provide further clarification if required.
