Forum Discussion

VivianC's avatar
VivianC
Copper Contributor
Oct 27, 2020

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:    
NameHoursLogged DateProjectCondition
Worker A0.512/25/2019BLANKCondition X
Worker A11/2/2020BLANKCondition Y
Worker A23/20/2020BLANKCondition Y
Worker B13/15/2020BLANKCondition Y
Worker B53/9/2020BLANKCondition Y
Worker B11/1/2020Project ACondition X
Worker C23/2/2020Project ACondition Y
Worker D34/2/2020Project BCondition X
     
Data table 2:    
 Project Start DateProject Complete Date  
Project A12/20/20193/10/2020  
Project B1/1/20204/1/2020  

Thank you!

6 Replies

No RepliesBe the first to reply

Resources