Forum Discussion

mikesdunbar's avatar
mikesdunbar
Icon for Helper I rankHelper I
2 years ago
Solved

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...
Need Help
  • amitchandak's avatar
    amitchandak
    2 years ago

    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]))

     

     

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
2 years ago

Hi,

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

mikesdunbar's avatar
mikesdunbar
Icon for Helper I rankHelper I
2 years ago

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.