Forum Discussion
Group data by start and end date
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!
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]))
4 Replies
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]))That did it, thank you!
Hi,
How did you arrive at the sales and hours figures in the third table?
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.
