Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
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.
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.
@parry2k Yeah, got some data wrong. Can you please remove the rows where Close date is earlier than the Application date
@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.
@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.
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |