Measure to display the "Personnel" last "Days to Close" based on the current Date filter

[Masikonde]

Hello Experts,

I’m trying to build a measure that will extract the last "Days to Close" from the "Personnel_contract_tracker" based on the latest date in the tracker. in this case "Days to close" is the average days it took for the contract team member to close on a contract. The measure should be able to select the team member, the last month or date and return the value of “Days to Close”

Below is a sample of the Personnel_Contracts_Tracker as I receive it.

Personnel	Month	Open Contracts	Closed Contracts	Days to Close	Threshold
Christine Mugoya	1/1/2018	5	0	43	60
Christine Mugoya	2/1/2018	4	0	53	60
Christine Mugoya	3/1/2018	2	2	41	60
Christine Mugoya	4/1/2018	1	1	70	60
Christine Mugoya	5/1/2018	2	0	94	60
Christine Mugoya	6/1/2018	4	1	50	60
Christine Mugoya	7/1/2018	3	1	82	60
Christine Mugoya	8/1/2018	2	1	56	60
Christine Mugoya	9/1/2018	3	1	42	60
Christine Mugoya	10/1/2018	2	2	33	60
Christine Mugoya	11/1/2018	0	1	25	60
Christine Mugoya	12/1/2018	3	0	66	60
Christine Mugoya	1/1/2019	7	1	40	60
Christine Mugoya	2/1/2019	3	2	54	60
Christine Mugoya	3/1/2019				60
Esther Chan	1/1/2018	6	4	51	60
Esther Chan	2/1/2018	7	2	49	60
Esther Chan	3/1/2018	8	0	71	60
Esther Chan	4/1/2018	4	3	75	60
Esther Chan	5/1/2018	13	0	29	60
Esther Chan	6/1/2018	11	7	31	60
Esther Chan	7/1/2018	7	5	26	60
Esther Chan	8/1/2018	7	2	41	60
Esther Chan	9/1/2018	35	11	26	60
Esther Chan	10/1/2018	46	15	28	60
Esther Chan	11/1/2018	43	15	35	60
Esther Chan	12/1/2018	43	4	46	60
Esther Chan	1/1/2019	17	4	37	60
Esther Chan	2/1/2019	25	8	25	60
Esther Chan	3/1/2019				60
Rober Johnny	1/1/2018	7	8	40	60
Rober Johnny	2/1/2018	12	1	32	60
Rober Johnny	3/1/2018	10	3	42	60
Rober Johnny	4/1/2018	10	4	26	60
Rober Johnny	5/1/2018	3	3	48	60
Rober Johnny	6/1/2018	1	1	12	60
Rober Johnny	7/1/2018	1	1	13	60
Rober Johnny	8/1/2018	1	3	15	60
Rober Johnny	9/1/2018	8	2	17	60
Rober Johnny	10/1/2018	10	2	23	60
Rober Johnny	11/1/2018	9	3	39	60
Rober Johnny	12/1/2018	7	2	54	60
Rober Johnny	1/1/2019	11	6	67	60
Rober Johnny	2/1/2019	13	4	45	60
Rober Johnny	3/1/2019				60
Chris K Davies	1/1/2018	7	2	18	60
Chris K Davies	2/1/2018	5	3	29	60
Chris K Davies	3/1/2018	3	3	42	60
Chris K Davies	4/1/2018	2	2	39	60
Chris K Davies	5/1/2018	5	0	40	60
Chris K Davies	6/1/2018	3	3	43	60
Chris K Davies	7/1/2018	3	3	22	60
Chris K Davies	8/1/2018	3	1	22	60
Chris K Davies	9/1/2018	5	0	29	60
Chris K Davies	10/1/2018	5	3	35	60
Chris K Davies	11/1/2018	3	3	54	60
Chris K Davies	12/1/2018	8	0	45	60
Chris K Davies	1/1/2019	8	3	45	60
Chris K Davies	2/1/2019	6	4	54	60
Chris K Davies	3/1/2019				60
Joice Carnes	1/1/2018	1	0	43	60
Joice Carnes	2/1/2018	1	1	63	60
Joice Carnes	3/1/2018	2	1	47	60
Joice Carnes	4/1/2018	2	1	58	60
Joice Carnes	5/1/2018	0	1	118	60
Joice Carnes	6/1/2018	0	0	0	60
Joice Carnes	7/1/2018	0	0	0	60
Joice Carnes	8/1/2018	1	0	16	60
Joice Carnes	9/1/2018	1	0	36	60
Joice Carnes	10/1/2018	1	1	32	60
Joice Carnes	11/1/2018	0	1	26	60
Joice Carnes	12/1/2018	6	2	45	60
Joice Carnes	1/1/2019	5	3	53	60
Joice Carnes	2/1/2019	5	2	53	60
Joice Carnes	3/1/2019				60
Bob Farmer	1/1/2018				60
Bob Farmer	2/1/2018				60
Bob Farmer	3/1/2018				60
Bob Farmer	4/1/2018				60
Bob Farmer	5/1/2018				60
Bob Farmer	6/1/2018				60
Bob Farmer	7/1/2018				60
Bob Farmer	8/1/2018				60
Bob Farmer	9/1/2018				60
Bob Farmer	10/1/2018	1	0	0	60
Bob Farmer	11/1/2018	3	1	68	60
Bob Farmer	12/1/2018	4	1	56	60
Bob Farmer	1/1/2019	3	2	45	60
Bob Farmer	2/1/2019	6	3	48	60
Bob Farmer	3/1/2019

[v-shex-msft · Accepted solution]

Hi @Masikonde ,

Please try to use following measure formula if it suitable for your requirement:

Measure =
VAR _currCate =
    MAX ( T1[Personnel] )
VAR _lastdate =
    CALCULATE ( MAX ( T1[Month] ), ALLSELECTED ( T1 ), VALUES ( T1[Personnel] ) )
RETURN
    CALCULATE (
        AVERAGE ( T1[Days to Close] ),
        FILTER ( ALLSELECTED ( T1 ), [Personnel] = _currCate && [Month] < _lastdate )
    )

Regards,

Xiaoxin Sheng