Forum Discussion
Calculate total on last return date
Hi
I'm trying to calculate the number of employees per customer. I am struggling with calculating the number of employees as the companies submitted a return sometimes quarterly or even annually. I need to calculate the number of employees from the last return. Below is sample anoymised data so for example the total for company A would be 16 but for company C it would be 116 from return date 31/12/2017. Any ideas on how to do this in DAX would be much appreciated. Thanks in advance.
Company No of Employees Return Date
| A | 16 | 31/12/2017 00:00 |
| B | 1 | 31/12/2017 00:00 |
| C | 116 | 31/12/2017 00:00 |
| C | 118 | 30/09/2017 00:00 |
| C | 124 | 30/06/2017 00:00 |
| C | 125 | 31/03/2017 00:00 |
| D | 25 | 27/09/2017 00:00 |
| E | 2 | 31/05/2017 00:00 |
| F | 5 | 03/08/2017 00:00 |
| G | 0 | 10/03/2017 00:00 |
| H | 421 | 31/12/2017 00:00 |
| H | 440 | 30/09/2017 00:00 |
| H | 448 | 30/06/2017 00:00 |
| H | 454 | 31/03/2017 00:00 |
| I | 5 | 30/04/2017 00:00 |
| J | 196 | 02/10/2017 00:00 |
| J | 632 | 01/10/2017 00:00 |
| J | 649 | 01/01/2017 00:00 |
| J | 655 | 01/04/2017 00:00 |
| J | 655 | 01/07/2017 00:00
|
Anonymous
Please see attached file
5 Replies
- Zubair_Muhammad
Community Champion
HI Anonymous
Try this MEASURE
Measure = CALCULATE ( SUM ( Table1[No of employees] ), LASTDATE ( Table1[Return Date] ) )
- Zubair_Muhammad
Anonymous
Please see attached file
v-piga-msftResident Rockstar
Hi Anonymous，
Have you solved your problem with the reply from Zubair_Muhammad?
If you have solved your problem, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If you still need help, please share your expected output so that we can help further investigate on it?
Best Regards，
Cherry