Sum By Week only have Start and End Date

[crawmike81]

Hi, 

I am trying to create a chart that will show totals by week but I only have start and end dates for each proposal. 

Below is my small sample of data. The start and finish come from proposal table and headcount and subunit come from proposal log detail table. They are linked by proposal id. I need to a chart that will sum the total headcounts by weekending. Below is example from excel. I know how to do it in exel using sumifs and creating but I cannot figure out how to do in powerbi. 

ProposalID  Start             Finish         Headcount  SubUnit ID

P20-0681	8/4/2020	8/4/2020	1	103
P20-0681	8/4/2020	8/4/2020	1	104
P20-0681	8/4/2020	8/4/2020	1	133
P20-0652	8/17/2020	9/14/2020	1	101
P20-0652	8/17/2020	9/14/2020	1	102
P20-0652	8/17/2020	9/14/2020	1	133
P20-0672	8/21/2020	11/20/2020	1	133
P20-0672	8/21/2020	11/20/2020	2	106
P20-0672	8/21/2020	11/20/2020	36	103
P20-0657	8/24/2020	10/5/2020	1	101
P20-0657	8/24/2020	10/5/2020	1	109
P20-0657	8/24/2020	10/5/2020	1	112
P20-0657	8/24/2020	10/5/2020	1	130
P20-0657	8/24/2020	10/5/2020	1	131
P20-0657	8/24/2020	10/5/2020	1	133
P20-0657	8/24/2020	10/5/2020	2	102
P20-0751	8/31/2020	9/14/2020	1	101
P20-0752	8/31/2020	9/14/2020	1	101
P20-0751	8/31/2020	9/14/2020	1	112
P20-0752	8/31/2020	9/14/2020	1	112
P20-0751	8/31/2020	9/14/2020	1	133
P20-0752	8/31/2020	9/14/2020	1	133
P20-0751	8/31/2020	9/14/2020	2	102
P20-0752	8/31/2020	9/14/2020	2	102
P20-0731	8/31/2020	9/28/2020	1	101
P20-0731	8/31/2020	9/28/2020	1	112
P20-0731	8/31/2020	9/28/2020	1	133
P20-0731	8/31/2020	9/28/2020	2	102

Thanks, 

[Anonymous · Accepted solution]

Hi @crawmike81,

It seems like a common analyst requirement between multiple date ranges, maybe you can take a look at the following link to create a expand bridge table then your records can be aggregated based on relationship:

Spread revenue across period based on start and end date, slice and dase this using different dates 

In addition, you can take a look at the below blog 'start date', 'end date' part to know more methods to handle this requirement.

Before You Post, Read This - trying to know how many of something you have on all of the dates betwe... 

Regards,

Xiaxoin Sheng