Hello, i'm quite new to PowerPivot/DAX in Excel and have a seemingly super simple question that I can manage to calculate in normal Excel formulas, but can't seem to get working in a PivotTable.
I have an extensive history of booked tasks with a date, daily description, a task number and an amount in USD. An example of this table is given below. This table is exported from an external program almost daily and therefore subject to changes. I have therefore added this to my datamodel using a 'Dynamic Range' using the Offset trick.
| Date | Daily_Description | Task_nr | Amount |
| 1-2-2023 | Direct sale | TK20 | 2000 |
| 1-3-2023 | Direct sale | TK10 | 1000 |
| 15-2-2023 | Extra sales from door-to-door-sales | XA | 2500 |
| 29-3-2023 | Direct sale | TK30 | 1000 |
| 7-2-2023 | Direct sale | TK10 | 3500 |
| 6-3-2023 | Add. sales from tradeshow | XA | 1200 |
| 21-3-2023 | Direct sale | TK10 | 3600 |
| 27-2-2023 | Direct sale | TK30 | 4800 |
| 5-3-2023 | Direct sale | TK10 | 2100 |
| 2-2-2023 | Direct sale | TK10 | 700 |
| 24-2-2023 | Direct sale | TK10 | 300 |
Most of these tasks are booked directly on a Task number, however some are booked on a 'general number' (XA) and need a manual allocation, based on the Daily_Description column to the correct Task number. This is done in a separate table, as shown below:
| Additional_sales_description | Related_Task_nr |
| Extra sales from door-to-door-sales | TK20 |
| Add. sales from tradeshow | TK10 |
Lastly, each Task comes with a specific task description, separate from the daily description. This task description is also manually entered in a separate table, as shown in the example below:
| Task_Description | Task_nr |
| Task Description 20 | TK20 |
| Task Description 10 | TK10 |
| Task Description 30 | TK30 |
What I would like now, is a PivotTable that shows all of these things combined per task for both a specific period and in total.
The PivotTable should combine the Task Description from table 3 for each task with the direct sales for a specific period (which is done by a PivotTable Timeline), as well as the indirect/additional sales per task from table 2.
The table should look like this with the period after March 1st, 2023 (01-03-2023). (Dates are in dd-mm-yyyy). Bold columns are columns I can't seem to get working.
| Task | Description | direct_sales_period | indirect_sales_period | direct_sales_total | all_sales_total |
| TK10 | Task Description 10 | 6700 | 1200 | 11200 | 12400 |
| TK20 | Task Description 20 | 0 | 0 | 2000 | 4500 |
| TK30 | Task Description 30 | 1000 | 0 | 5800 | 5800 |
The direct_sales_period is just a regular SUM with a filter and the direct_sales_total is a SUM with ALL dates.
However, the indirect_sales_period and all_sales_total rely on a link with table 2 and I can't seem to get it working with either a Relationship (RELATED) or a LOOKUPVALUE. Even for the simple Task Description from table 3, it doesnt seem to work. What is the correct way to approach this? In regular Excel formulas I would just use some SUMIFS.
