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

     

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
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]))

 

 

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

That did it, thank you!