Group data by start and end date

[mikesdunbar]

I want to be able to measure (avg, sum, high, low) values (dollars, hours) based on a start and end date. I have two tables:

Raw Data [Table 1]

Date	Person	Hours	Sales
1/5/2023	Joe	20	1000
2/5/2023	Joe	15	1500
3/5/2023	Joe	15	2000
4/5/2023	Joe	25	3000
5/5/2023	Joe	20	1000
6/5/2023	Joe	15	5000
1/5/2023	Mary	20	1000
2/5/2023	Mary	15	1500
3/5/2023	Mary	15	2000
4/5/2023	Mary	25	3000
5/5/2023	Mary	20	1000
6/5/2023	Mary	15	5000

Phase Dates [Table 2]

Phase	Start	End
Phase 1	1/1/2023	2/28/2023
Phase 2	3/1/2023	4/30/2023
Phase 3	5/1/2023	6/30/2023

I'd like to have a table where I can have all the hours and sales summed per phase. Something like this:

Phase	Hours	Sales
Phase 1	70	5000
Phase 2	80	10000
Phase 3	70	12000

It would also be nice to have those phases in a slicer to filter other data too. 

Thanks!

[Ashish_Mathur]

Hi,

How did you arrive at the sales and hours figures in the third table?

[mikesdunbar]

Hi Ashish,

I realize now that the tables are hard to make out without borders between cells. Table 2 lists all the Phases and their respective start and finish dates e.g. Phase 1 starts 1/1/2023 and finishes 2/28/2023. That final table sums the hours and sales based on the date range. 

On the subject of unclear tables, do you know how I could add borders? The tables were inserted here and I tried to do advanced table properties and it didn't do anything. 

[amitchandak · Accepted solution]

@mikesdunbar , You can try a measure like

Sum Sales = Sumx('Table 2', Sumx(FILTER('Table 1', 'Table 1'[Date] >= 'Table 2'[Start] && 'Table 1'[Date] <= 'Table 2'[End]), 'Table 1'[Sales]))

Sum Hours = Sumx('Table 2', Sumx(FILTER('Table 1', 'Table 1'[Date] >= 'Table 2'[Start] && 'Table 1'[Date] <= 'Table 2'[End]), 'Table 1'[Hours]))

[mikesdunbar]

That did it, thank you!