Calculate SUM of allocation over a related table with dynamic year filter
Hello, I am having troubles calculating the sum of Allocation from a related table, based on the year filter selection. I have the following SharePoint lists (I used Excel to simplify): tblAllocation Start_Date End_Date Year_A Allocation_in_Days Employee Project 01-01-21 31-12-21 2021 40 Anna Project 1 01-01-21 31-12-21 2021 135 Linda Project 1 01-01-22 31-12-22 2022 60 Anna Project 1 01-01-22 31-12-22 2022 95 Linda Project 1 01-01-22 31-12-22 2022 15 Hans Project 1 tblProjects Title Start Date End Date Project 1 19-10-20 30-12-22 tblTasks Title Project Start Date Due Date Cost In Days Task 1 Project 1 10-05-21 06-08-21 95 Task 2 Project 1 10-01-22 25-02-22 30 Task 3 Project 1 16-05-22 10-06-22 15 Task 4 Project 1 06-06-22 17-06-22 0 Task 5 Project 1 30-05-22 03-06-22 0 Task 6 Project 1 30-08-21 10-12-21 35 Task 7 Project 1 25-04-22 06-05-22 14 Task 8 Project 1 09-05-22 13-05-22 5 Task 9 Project 1 20-06-22 24-06-22 5 Task 10 Project 1 28-02-22 18-04-22 31 What I would like is to display in a Power BI table the following: Title (tblProjects) Start Date (tblProject) End Date (tblProjects) Cost In Days (tblTasks) Allocation_in_Days (tblAllocation) Project 1 19-10-20 30-12-22 230 345 The relationships are: -tblProjects-tblTasks: 1 to many -tblProjects-tblAllocation: 1 to many And have a filter for the tblTasks Start Date year: Whenever I select a year, the Allocation_in_Days should be calculated accordingly (example: if I select year 2021, the Allocation_in_days should be 175). That is now not the case; How can I do that? Thank you!
