cancel
Showing results for
Did you mean:
Frequent Visitor

## How many closed within 60 days? How many closed within 90 days?

how many had closed within 30 days? How many closed within 60 days? How many closed within 90 days? It might look something like this…Applications taken in October = 30

Loans closed within 30 days of application date = 10, therefore 30 day pull through rate = 33.33%

Loans closed 31-60 days of application date = 15, therefore the 60 day pull through rate = 25 (10+15) / 30 = 83.33%

Loans closed 61-90 days of application date = 2, therefore the 90 day pull through rate = 27 (10_15+2) / 30 = 90.0%

And perhaps that’s difficult to do, so I’d offer this alternative way to look at pull through:  Look at Application month (or loan started month) +1, month, +2 months, +3 months.  So in that set up, we’d look at all October applications (1st-31st) and see how many had closed by November 30th, how many more by December 30th, and how many more by January 31st.

Hopefully that makes sense. It’s about being able to predict how many loans will close in the future (1, 2, 3 months out) based on how many loans are in the pipeline (as defined either by start date or application date) today, or at the beginning of a new month.

 Loan # Loan Amount File start Date Application Date Closed Date 40 191165 6/20/2017 1/17/2013 7/11/2017 52 209520 1/9/2017 8/25/2016 8/10/2017 75 384750 8/14/2017 10/30/2015 10/13/2017 81 424100 6/26/2017 9/15/2017 7/31/2017 91 251750 6/14/2017 4/8/2016 7/7/2017 93 265109 2/28/2017 2/23/2016 3/31/2017 108 174775 1/12/2017 10/6/2020 3/20/2017 142 35459 5/10/2017 1/15/2020 7/17/2017 153 100000 7/13/2016 4/4/2017 11/14/2016 173 476098 2/16/2017 7/24/2012 4/27/2017 175 196000 5/16/2017 5/31/2017 6/30/2017 177 246500 10/5/2016 6/27/2017 2/2/2017 192 105600 4/21/2016 7/11/2017 6/24/2016 195 233750 1/12/2017 5/17/2017 5/22/2017 196 84875 5/23/2017 5/9/2017 8/25/2017 212 247300 7/24/2012 2/24/2017 9/28/2012 222 114141 6/7/2017 5/3/2017 7/28/2017 228 380000 2/2/2017 4/24/2017 7/7/2017 232 210123 9/29/2016 2/17/2017 1/25/2017 244 172958 6/15/2017 6/5/2017 8/10/2017 259 97034 3/14/2017 10/18/2016 8/1/2017 264 66174 1/11/2017 6/30/2017 4/6/2017 274 162993 7/17/2017 4/18/2017 8/21/2017 280 200000 11/25/2016 4/10/2017 1/11/2017 281 161000 5/16/2017 6/23/2017 6/30/2017 284 125130 2/23/2017 5/17/2017 4/19/2017 302 268050 1/1/2001 5/10/2017 10/18/2012 319 229761 6/6/2017 3/23/2017 8/7/2017 339 246489 5/8/2017 6/5/2017 7/28/2017 341 96900 4/24/2017 6/14/2017 6/29/2017 350 213600 12/1/2015 11/8/2016 3/28/2016 351 227000 1/21/2016 10/17/2017 3/22/2016 355 484350 8/26/2019 2/23/2015 10/10/2019 356 200000 6/14/2016 5/18/2017 7/25/2016 357 417000 3/14/2016 10/9/2018 7/22/2016 362 307000 6/13/2019 11/7/2018 7/30/2019 388 224258 6/19/2015 8/25/2017 9/24/2015 389 210000 8/27/2015 5/26/2016 9/30/2015 390 198400 8/9/2016 5/25/2016 9/29/2016 391 328500 8/27/2015 3/31/2016 9/25/2015 426 292500 4/14/2016 8/9/2018 5/11/2016 431 313200 1/6/2016 9/12/2017 2/9/2016 432 356000 4/16/2016 10/22/2015 5/24/2016 433 237000 4/6/2016 4/22/2016 6/17/2016 434 296000 2/12/2016 11/30/2015 4/29/2016 435 394250 1/16/2019 3/11/2016 5/31/2019 466 288000 8/2/2016 8/30/2017 9/23/2016 468 417000 6/2/2016 1/15/2019 8/5/2016 470 195376 8/17/2015 8/28/2015 11/5/2015 471 196000 9/22/2015 10/16/2018 12/21/2015
1 ACCEPTED SOLUTION
Community Support

Hi, @NXD210907

1. Create calculated column to get category and year-month

``````Category =
var _datediff=DATEDIFF([Application Date],[Closed Date],DAY)
var _Cat=
SWITCH(
TRUE(),
_datediff<=30,3,//0~30
_datediff<=60,6,//31~60
_datediff<=90,9,//61~90
91)//>90
return _Cat``````
``Application Year-Month = FORMAT([Application Date],"YYYY-MM")``

2. Create measures to get the total number of loans for the month

``````_CountOfLoanByMonth =
var _NumberInMonth=
CALCULATE(COUNT('Table'[Loan #]),ALLEXCEPT('Table','Table'[Application Year-Month]))
return _NumberInMonth``````

3. Create measures to calculate the pull through rate for 30, 60, and 90 days from the application date

``````_30 =
var _currentCat=MAX('Table'[Category])

var _cat3={3}
var _cat6={3,6}
var _cat9={3,6,9}

var _CountOfTotal=
CALCULATE(COUNT('Table'[Loan #]),ALLEXCEPT('Table','Table'[Application Year-Month]))

var _CountOf30=
CALCULATE(
COUNT('Table'[Loan #]),FILTER(ALLEXCEPT('Table','Table'[Application Year-Month]),'Table'[Category] in _cat3))
var _CountOf60=
CALCULATE(
COUNT('Table'[Loan #]),FILTER(ALLEXCEPT('Table','Table'[Application Year-Month]),'Table'[Category] in _cat6))
var _CountOf90=
CALCULATE(
COUNT('Table'[Loan #]),FILTER(ALLEXCEPT('Table','Table'[Application Year-Month]),'Table'[Category] in _cat9))

var _showCount3=IF(_currentCat in _cat3,_CountOf30)
var _showCount6=IF(_currentCat in _cat6,_CountOf60)
var _showCount9=IF(_currentCat in _cat9,_CountOf90)

var _result30=DIVIDE(_showCount3,[_CountOfLoanByMonth])
var _result60=DIVIDE(_showCount6,[_CountOfLoanByMonth])
var _result90=DIVIDE(_showCount9,[_CountOfLoanByMonth])

return _result30``````

Result:

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 REPLIES 6
Community Support

Hi, @NXD210907

1. Create calculated column to get category and year-month

``````Category =
var _datediff=DATEDIFF([Application Date],[Closed Date],DAY)
var _Cat=
SWITCH(
TRUE(),
_datediff<=30,3,//0~30
_datediff<=60,6,//31~60
_datediff<=90,9,//61~90
91)//>90
return _Cat``````
``Application Year-Month = FORMAT([Application Date],"YYYY-MM")``

2. Create measures to get the total number of loans for the month

``````_CountOfLoanByMonth =
var _NumberInMonth=
CALCULATE(COUNT('Table'[Loan #]),ALLEXCEPT('Table','Table'[Application Year-Month]))
return _NumberInMonth``````

3. Create measures to calculate the pull through rate for 30, 60, and 90 days from the application date

``````_30 =
var _currentCat=MAX('Table'[Category])

var _cat3={3}
var _cat6={3,6}
var _cat9={3,6,9}

var _CountOfTotal=
CALCULATE(COUNT('Table'[Loan #]),ALLEXCEPT('Table','Table'[Application Year-Month]))

var _CountOf30=
CALCULATE(
COUNT('Table'[Loan #]),FILTER(ALLEXCEPT('Table','Table'[Application Year-Month]),'Table'[Category] in _cat3))
var _CountOf60=
CALCULATE(
COUNT('Table'[Loan #]),FILTER(ALLEXCEPT('Table','Table'[Application Year-Month]),'Table'[Category] in _cat6))
var _CountOf90=
CALCULATE(
COUNT('Table'[Loan #]),FILTER(ALLEXCEPT('Table','Table'[Application Year-Month]),'Table'[Category] in _cat9))

var _showCount3=IF(_currentCat in _cat3,_CountOf30)
var _showCount6=IF(_currentCat in _cat6,_CountOf60)
var _showCount9=IF(_currentCat in _cat9,_CountOf90)

var _result30=DIVIDE(_showCount3,[_CountOfLoanByMonth])
var _result60=DIVIDE(_showCount6,[_CountOfLoanByMonth])
var _result90=DIVIDE(_showCount9,[_CountOfLoanByMonth])

return _result30``````

Result:

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

@parry2k Yeah, got some data wrong. Can you please remove the rows where Close date is earlier than the Application date

Super User

@NXD210907 that's what I thought but some of the closed dates are prior to the application date, how is that possible? or it is an issue with the dummy data.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Super User

@NXD210907 what are the key dates here in your sample data to calculate the number of days?

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Frequent Visitor

Application Date and Closed Date should be the key dates. I need to know the number of loans closed 30, 60, 90 days from Application Date.

So basically, if a loan has an application date of July 01, 2021,  and closing date of July 25, 2021 then it should be in 30 days category. If the same close has closing date of Aug 25, 2021 then it should be in 60 days category.

Super User

@NXD210907 I would recommend pasting sample data with the expected output.  Read this post to get your answer quickly.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Announcements