Forum Replies Created
-
Posts
-
…should also add, I really don’t need to track new accounts which takes care of one item. Really just need to capture those accounts present in first period that are not present in the last period, which would be classified as “Closed”. Went back to the drawing board on this using the old reliable index/match formula in a non-PowerPivot environment. Was able to replicate the output by building two tables comparing the risk grades of the earliest data set vs. the risk grades of the latest data set and building a pivot table. I have attached the workbook. I know this might be blasphemy, but I wonder if I have a situation here where it’s just to much work to get PowerPivot to churn out a result and if I’m better off with just the manual work around.
I’ve started picking through the workbook. Are the new fields that I see in the PowerPivot table view, values that you created through Power Query? I see your points of the “why” this is difficult. Not sure I understand why the transactions in between would need to be handled as it really doesn’t matter what happens in between. In other words if an account migrated from a 4 at the starting period to a 5 in an interim period but ended up back at a 4 at the ending period then the account number would show no migration as the focus is really just on the beginning view vs. how the the data set ended up in the final period.
Hey Tom, no worries at all. I am very grateful for any work you have put forth, for absolutely free! I’ll definitely take a look at what you have built, maybe it will get me going in the right direction towards a final product. Just FYI, this is a very critical process for banks in the area of credit risk management. Every bank larger than the local mom and pop runs this sort of anlaysis, always in SAS or some other database query tool. I’m trying to drive PowerPivot as the go to solution in my organization and this is a critical piece, so kudos for your help to this end. I’ll share the final product with you if and when I am able to get it to the finish line. Thanks again for your assistance.
No problem.
Thanks for this Tom. I think you are on to something as what I really needed to do was to reconsider the way in which I was building the report. I have attached a workbook that shows how it could be layed out in a pivot. The row header is FP Rating which is “First Period Rating” grouped by “Category”. The column header is the “LP Rating” which is Last Period rating. There are also two other columns to quantify the number of accounts that have closed and opened since the first reporting date. I think this is the best way to report migration by count. Also interested in running a similar table based on balances. Would have to think through which balance to show, first period or last period… but believe I could build a balance measure by replicating the “count” measures that you have built. Hopefully this helps and am curious what if anything would need to be modified in what you have built to allow it to work in the manner provided in the example?
Any luck on this Tom?
Finally getting back to this… I’ve attached a sample workbook. As you will see there is a “Reporting Date” field which in my real core which goes back many multiple years. Also have “Rating” which is ultimately what we are trying to measure in the way of movement between two selected reporting periods to be totaled in count by the “Category” field. The Output worksheet that I created attempts to show what I want Powerpivot to isolate which is the # of accounts per Rating as of a particular reporting date as compared to the # of accounts per Rating as of a particular later reporting date. The dates will be chosen via a Timeline Slicer so would assume that its going to need to use ALLSELECTED() as you have shown me before. Also need to report the migration in dollars as of the initial date identifying the $ movement in rating per row (Category). This assumes dollars are static over the period selected, which is not the case, however not sure how else to report it as a date for the balances has to be determined. Just to throw another wrinkle into this, I’m also looking for a way to isolate accoutns that have closed ( in this case they literally disappear from the data set). For example, if an account is present 07/31/15 at $60,000 and it is closed on 10/15/15 it would no longer appear in the 10/31/15 reporting date set. I have not included a closed account scenario in the attached workbook as all of the accounts continue through in each reporting date set. It could be replicated by just deleting an account in the source file. Thanks for your help Tom!
Quite a meaty post. I remember reading through a portion of that post some time ago. I don’t work with P&L or Balance sheet data specifically from an accounting perspective however I can definitely see a correlation of the challenges of someone working with aggregating loan level data by various categories as I do with aggregating from a general ledger. Have you built something similar to what’s described in the post? Any hanging issues or items that you haven’t been able to solve?
#3 was the culprit. Constructed the “Year-Month” column in a manner that inadvertnently forced a sort similar to the order you described in #2. I’m up and running, thanks again Tom!
Found a bug…in my version. Tested your version by changing moving slicer dates all around and it worked flawlessly. My side works fine except for certain instances where it wants to grab a random date in the list and call it the first date. FYI, my date table is sorted by column by a year date field. I thought that might be it, but doesn’t appear to be so. One example of the issue is if I grab April 2014 (which is the actual first date where my data exists) through September 2014 it works fine. If I slim the date range to May 2014 through September 2014 it thinks that August 2014 is the firstnonblank and grabs the balance associated with that date. Any combination of dates that exclude April 2014 and include August 2014 will show August 2014 as the first nonblank. This is also the case for December 2014 if I exclude August 2014 and include any range of dates to include December 2014, I get that date as the firstnonblank. Very strange. I know it’s probably difficult to decipher without seeing the workbook, any ideas?
Good idea, will start a new string. FYI, sent you a LinkedIn invite such that you can feel free to refer any PP users in banking industry that want to connect. Thanks again.
That did it! I was close and actually looked into both FIRSTNONBLANK and ALLSELECTED as possibilities but could never work out the right combination. This is a powerful combo and really allows users to customize the date frame while recalibrating the starting point for % delta analysis. Will now drop various categories on column axis which makes this tremedously powerful…along with other slicers.
On a side note, do you know of any other Power Pivot users that are in Banking? While the Powerpivotpro blog and print materials are very helpful it would be interesting to talk with other users in the Banking industry who are using Power Pivot. Most of the examples found in print and online are related to sales related data and Banking is of course more of a date and balance type and category data set. Of course with its very own language as well. Just curious, thanks again for your help!
OK, I tried it and realized that I have done something similar to what you recommended here with the same result as provided in the attached workbook. I have changed the numbers presented below to protect company info but the issue is still evident which is that [Balance on First Date] is just returning the same number as the [Balance] measure by month. I have attached and example of the results as well as two examples of desired result.
Thanks Tom. There is a calendar table and my first run at this included FIRSTNONBLANK() on my Calendar[Date] field however I don’t believe my final measure was the equivalent of what you have here. Will give it a try and let you know the results.
If anyone, “umhmm….Tom…” can tell me how to solve this with a measure I would be very interested as I know that measures are preferable to calculated columns with regard to performance. Thanks.
