Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
NXD210907
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 AmountFile start DateApplication DateClosed Date
401911656/20/20171/17/20137/11/2017
522095201/9/20178/25/20168/10/2017
753847508/14/201710/30/201510/13/2017
814241006/26/20179/15/20177/31/2017
912517506/14/20174/8/20167/7/2017
932651092/28/20172/23/20163/31/2017
1081747751/12/201710/6/20203/20/2017
142354595/10/20171/15/20207/17/2017
1531000007/13/20164/4/201711/14/2016
1734760982/16/20177/24/20124/27/2017
1751960005/16/20175/31/20176/30/2017
17724650010/5/20166/27/20172/2/2017
1921056004/21/20167/11/20176/24/2016
1952337501/12/20175/17/20175/22/2017
196848755/23/20175/9/20178/25/2017
2122473007/24/20122/24/20179/28/2012
2221141416/7/20175/3/20177/28/2017
2283800002/2/20174/24/20177/7/2017
2322101239/29/20162/17/20171/25/2017
2441729586/15/20176/5/20178/10/2017
259970343/14/201710/18/20168/1/2017
264661741/11/20176/30/20174/6/2017
2741629937/17/20174/18/20178/21/2017
28020000011/25/20164/10/20171/11/2017
2811610005/16/20176/23/20176/30/2017
2841251302/23/20175/17/20174/19/2017
3022680501/1/20015/10/201710/18/2012
3192297616/6/20173/23/20178/7/2017
3392464895/8/20176/5/20177/28/2017
341969004/24/20176/14/20176/29/2017
35021360012/1/201511/8/20163/28/2016
3512270001/21/201610/17/20173/22/2016
3554843508/26/20192/23/201510/10/2019
3562000006/14/20165/18/20177/25/2016
3574170003/14/201610/9/20187/22/2016
3623070006/13/201911/7/20187/30/2019
3882242586/19/20158/25/20179/24/2015
3892100008/27/20155/26/20169/30/2015
3901984008/9/20165/25/20169/29/2016
3913285008/27/20153/31/20169/25/2015
4262925004/14/20168/9/20185/11/2016
4313132001/6/20169/12/20172/9/2016
4323560004/16/201610/22/20155/24/2016
4332370004/6/20164/22/20166/17/2016
4342960002/12/201611/30/20154/29/2016
4353942501/16/20193/11/20165/31/2019
4662880008/2/20168/30/20179/23/2016
4684170006/2/20161/15/20198/5/2016
4701953768/17/20158/28/201511/5/2015
4711960009/22/201510/16/201812/21/2015
1 ACCEPTED SOLUTION
v-angzheng-msft
Community Support
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:

vangzhengmsft_0-1643080339500.png

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.

View solution in original post

6 REPLIES 6
v-angzheng-msft
Community Support
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:

vangzhengmsft_0-1643080339500.png

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.

NXD210907
Frequent Visitor

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

parry2k
Super User
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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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.

parry2k
Super User
Super User

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

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.