cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Masikonde Frequent Visitor
Frequent Visitor

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

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.

PersonnelMonthOpen ContractsClosed ContractsDays to CloseThreshold
Christine Mugoya1/1/2018504360
Christine Mugoya2/1/2018405360
Christine Mugoya3/1/2018224160
Christine Mugoya4/1/2018117060
Christine Mugoya5/1/2018209460
Christine Mugoya6/1/2018415060
Christine Mugoya7/1/2018318260
Christine Mugoya8/1/2018215660
Christine Mugoya9/1/2018314260
Christine Mugoya10/1/2018223360
Christine Mugoya11/1/2018012560
Christine Mugoya12/1/2018306660
Christine Mugoya1/1/2019714060
Christine Mugoya2/1/2019325460
Christine Mugoya3/1/2019   60
Esther Chan1/1/2018645160
Esther Chan2/1/2018724960
Esther Chan3/1/2018807160
Esther Chan4/1/2018437560
Esther Chan5/1/20181302960
Esther Chan6/1/20181173160
Esther Chan7/1/2018752660
Esther Chan8/1/2018724160
Esther Chan9/1/201835112660
Esther Chan10/1/201846152860
Esther Chan11/1/201843153560
Esther Chan12/1/20184344660
Esther Chan1/1/20191743760
Esther Chan2/1/20192582560
Esther Chan3/1/2019   60
Rober Johnny1/1/2018784060
Rober Johnny2/1/20181213260
Rober Johnny3/1/20181034260
Rober Johnny4/1/20181042660
Rober Johnny5/1/2018334860
Rober Johnny6/1/2018111260
Rober Johnny7/1/2018111360
Rober Johnny8/1/2018131560
Rober Johnny9/1/2018821760
Rober Johnny10/1/20181022360
Rober Johnny11/1/2018933960
Rober Johnny12/1/2018725460
Rober Johnny1/1/20191166760
Rober Johnny2/1/20191344560
Rober Johnny3/1/2019   60
Chris K Davies1/1/2018721860
Chris K Davies2/1/2018532960
Chris K Davies3/1/2018334260
Chris K Davies4/1/2018223960
Chris K Davies5/1/2018504060
Chris K Davies6/1/2018334360
Chris K Davies7/1/2018332260
Chris K Davies8/1/2018312260
Chris K Davies9/1/2018502960
Chris K Davies10/1/2018533560
Chris K Davies11/1/2018335460
Chris K Davies12/1/2018804560
Chris K Davies1/1/2019834560
Chris K Davies2/1/2019645460
Chris K Davies3/1/2019   60
Joice Carnes1/1/2018104360
Joice Carnes2/1/2018116360
Joice Carnes3/1/2018214760
Joice Carnes4/1/2018215860
Joice Carnes5/1/20180111860
Joice Carnes6/1/201800060
Joice Carnes7/1/201800060
Joice Carnes8/1/2018101660
Joice Carnes9/1/2018103660
Joice Carnes10/1/2018113260
Joice Carnes11/1/2018012660
Joice Carnes12/1/2018624560
Joice Carnes1/1/2019535360
Joice Carnes2/1/2019525360
Joice Carnes3/1/2019   60
Bob Farmer1/1/2018   60
Bob Farmer2/1/2018   60
Bob Farmer3/1/2018   60
Bob Farmer4/1/2018   60
Bob Farmer5/1/2018   60
Bob Farmer6/1/2018   60
Bob Farmer7/1/2018   60
Bob Farmer8/1/2018   60
Bob Farmer9/1/2018   60
Bob Farmer10/1/201810060
Bob Farmer11/1/2018316860
Bob Farmer12/1/2018415660
Bob Farmer1/1/2019324560
Bob Farmer2/1/2019634860
Bob Farmer3/1/2019    

 

1 REPLY 1
Community Support Team
Community Support Team

Re: Measure to display the "Personnel" last "Days to Close" based on the current

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |