Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
105 | |
79 | |
69 | |
62 |
User | Count |
---|---|
142 | |
105 | |
103 | |
85 | |
70 |