Calculate total on last return date

[Anonymous]

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

[v-piga-msft]

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

[Zubair_Muhammad]

HI @Anonymous

Try this MEASURE

Measure =
CALCULATE ( SUM ( Table1[No of employees] ), LASTDATE ( Table1[Return Date] ) )

[Zubair_Muhammad · Accepted solution]

@Anonymous

Please see attached file

[Anonymous]

Thank you @Zubair_Muhammad - this works perfectly.

Kind regards

[Anonymous]

Hello

Just building on my previous question

how would I calculate the total on the return data previous to the last date?

Thanks

Jo.