Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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]))
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.
@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!
User | Count |
---|---|
99 | |
86 | |
78 | |
75 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |