This topic contains 14 replies, has 2 voices, and was last updated by 
-
Posts
-
I am working on what amounts to something similar to a Cohort analysis although I don’t think it’s exactly what Rob described in a post regarding Cohort which leveraged the PRODUCTX( ) function found in XL 2016 it very well could be the same.
I have a core table that includes reporting date (sequential month end dates), account number, balance, and category. What I am attempting to do is provide information in the way of balances that have migrated between categories over a custom period of time (time slicer connected to pivot table). An example would be two accounts labeled as category “X” which total $100 as of June 30,2015. Let’s assume that 3 months later (09/30/15) that one account worth $60 (balance as of 06/30/15) is now classified as category “Y”. I would like to be able to report that over the period selected $40 remained as category “X” and $60 migrated to category “Y”. Anyone have any thoughts, or solved for something like this?
Mike,
Sounds quite familiar…
Should be do-able, but to some extent depends on what you want your report to look like. Could you put together a few rows in a workbook showing: 1) what columns would be in the source data and 2) what you want the finished report to look like?
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!
Any luck on this Tom?
Mike,
Yes.
Identifying migrations turned out to be relatively easy in Power Query: creating a copy of the dataset and, for each account number, joining a “current” period to the immediately preceding period did the trick.
Identifying closing periods and creating Power Query records for them (Power Pivot works best with records that are physically present) was also relatively easy: isolating the accounts that do not continue on to the end of last period of the dataset, then creating a “next” record after their last reporting period.
On Friday, I did run into a temporary setback. While getting ready to append the records for closing periods into another Power Query data set (a set that had the “original” records plus migration dates), Power Query said I duplicated an already existing ID. I have worked with many combined sets in Power Query before without a problem, so temporarily this one has put me into investigation mode.
I expect to resolve the duplicate ID issue later today, so tomorrow we should have something to talk about.
Something else to consider: For the reports where you want to create date references in Excel for what is going on inside of a “Power Pivot” pivot manipulated by a slicer (or slicers), regular slicers expose a reference that can be used in formulas, but I believe timeline slicers do not. I am using two regular slicers (a start period and an end period slicer).
Tom
Mike,
Please see attached workbook.
I am getting a message “unable to update the linguistic schema” message which I understand means that there is an issue between Power Query and Power Pivot. I will move to the queries to another workbook and see if error persists.
However, in the meantime, you can look at the queries and Power Pivot model.
I think I need some help understanding on how you would formulate a measure for net migration by count. I originally thought (and still do) that a migration is something that happens to an account: it changes category (which can be easily captured). But from the example on the Output tab, migration appears to be something else (given my understanding, there were no migrations in the original data set). If you could explain the steps you would take to count net migrations, that would be helpful.
Tom
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?
Mike,
Could you put up your attachment again, accidentally removed instead of downloaded…
Tom
No problem.
Thanks,
Tom
Mike,
Attached you will find a workbook that has my work so far. At this point, I have used Power Query to shape and reshape the original data to establish new, close and migration information.
While I think it is possible to write DAX code to write the results in a format as you requested, I now see those calculations to require more time than is available to me. I think the DAX code would be long and complex base on these two combined factors: 1) individual rows are not additive and 2) when working with only first and last periods, the transactions in-between would have to be accounted for (do-able, but require extra handling).
I apologize for the wait, data was originally posted on November 12 and today is the 23rd, but the end product is just not coming together for me.
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.
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.
…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.
Mike,
Perhaps more than anything else, I feel limited in this scenario. I struggle to picture the strategy of joining a begin set to an end set inside of Power Pivot that will yield to an easy to maintain group of measures.
It is just easier for me to picture a set of columns like IsNew, IsOpen, IsMigrated and doing a count against those dynamically within begin and end constraints.
I may figure it out later, but for now, I am glad you found a way for it to work in regular Excel.
Tom
The forum ‘Power Pivot’ is closed to new topics and replies.