Forum Discussion
Sum Array based on Date Range (Date Range Changes per Dimension)
Hi, if someone can help me sum as below, I find it difficult to sum by each project's date range, it may need to use array - I can't get it work. (I posted to Power BI forum as well because that is what my team wants, I am thinking I can at least use Excel to get the table if it can't be done in Power BI by Dax )
I want to sum hours linked to no project (=BLANK project), but within each Project's start and end range. And by person, by project.
Sumifs won't work.
This sample data is simple, only has two project in it, so we can point to that specific cell to get that project's time range. But in real data. the Project are hundreds, and the name is like "Timesheet Sept", "Store In the East", random names. So the formula needs to somehow look for the project's start and end date based on its name; then use its start/end date to sum the hours that NOT linked to any project.
Data for copying:
| Data table 1: | ||||
| Name | Hours | Logged Date | Project | Condition |
| Worker A | 0.5 | 12/25/2019 | BLANK | Condition X |
| Worker A | 1 | 1/2/2020 | BLANK | Condition Y |
| Worker A | 2 | 3/20/2020 | BLANK | Condition Y |
| Worker B | 1 | 3/15/2020 | BLANK | Condition Y |
| Worker B | 5 | 3/9/2020 | BLANK | Condition Y |
| Worker B | 1 | 1/1/2020 | Project A | Condition X |
| Worker C | 2 | 3/2/2020 | Project A | Condition Y |
| Worker D | 3 | 4/2/2020 | Project B | Condition X |
| Data table 2: | ||||
| Project Start Date | Project Complete Date | |||
| Project A | 12/20/2019 | 3/10/2020 | ||
| Project B | 1/1/2020 | 4/1/2020 |
Thank you!