I do lots of live training, face to face and online, and I always encourage students to bring along any business problems they would like some help with. Last week I had a great problem that Debbie shared with me that I thought was worth creating this blog article. I have modified the back story a bit, but the concept is still the same.
Phasing Income to Future Dates
Take the following situation. There are policies (think of them as contracts) that have a policy start date. The policy has a total value, but you want to phase the timing of the cash flows over a 12 month period commencing in the first month. As always, such problems cannot easily be solved without some good quality test data. I set up the following test data in Power Pivot for Excel. The phasing in this example is always a defined % for each month as shown in the phasing table below.
Note how my test data spans more than 12 months (ie I have at least 1 contract with a policy start date that is more than 12 months after the first) and the value of each policy is different, yet easy to conceptualise. Good test data is essential to solving complex problems (all problems actually). I wanted to make sure I could identify issues that may only show up in the second year and also issues that may be hidden if I set all policies to the same value.
Where to Start?
This turned out to be quite a tricky problem. There are 2 types of problems in DAX:
- Those that you already understand how to solve conceptually (because you have solved similar problems in the past or you know where to find a solution)
- Those that you have never solved before.
For me, this was the second type. Although I hadn’t seen such a problem, I did have some ideas on how I would approach it. My high level plan was:
- Build some test data
- Place the data in a pivot table so I could “see” what was going on
- Get the phasing working in a sequential way (eg month 1, month 2) ignoring exactly “when” the money would come in.
- Push the phasing out (somehow) to align the incoming money to the actual month.
Frankly I had no idea what the exact solution would be when I started.
One of the biggest differences between Power Pivot/DAX and Excel is the data is hidden in tables under the hood (in the data model). In my view, the only way to solve such problems is to create a visualisation that shows that hidden data in a meaningful way so you can “see” what is going on. So ALWAYS create an appropriate pivot table or a Matrix (in Power BI) to help.
Relationship Free Zone
Originally when I started with this problem I created a relationship between the Policy Date and the Calendar table. After some testing and failures I decided to remove this relationship and instead use a disconnected table model.
Chop the Elephant into Pieces
This is a tricky problem and I didn’t know how to solve it. All I knew was I needed to solve one part of the puzzle at a time, then move on to the next bit.
Begin with the End in Mind
I actually sketched out the final report on paper before I started so I was clear what I was trying to achieve. Here is the final result of my work to help you visualise where I was heading.
The rest of this article explains how I went about solving the problem.
Month ID of Policy Start Date
The first thing I decided to do was work out which was the first month of the policy. I used LOOKUPVALUE to do this because the Calendar table was not connected to the policy table. Instead of returning the actual month name, I returned the unique MonthID from my calendar table. I ALWAYS load a unique MonthID in my calendar tables as they are REALLY useful. In short this column is an integer, starting at 1 and incrementing by 1 for each month in the table. So starting in Jan, after 3 full years the MonthID will be 36.
Pol First Month = LOOKUPVALUE( 'Calendar'[Month Index], 'Calendar'[Date], MAX(Policies[Pol Date]) )
After writing this measure (and all subsequent measures), I placed the measure in the pivot table shown below. This pivot table has the policy date from the policy table on rows and the YYMM from the Calendar table on columns. I did this for a very important reason. I knew this would be the way I needed to see the final results and therefore I needed to make sure all measures returned the expected outcome once this layout was used. And don’t think I wrote the all the formulas correctly to achieve this outcome the first time. In fact I made plenty of mistakes. But the point is I wouldn’t have known they were mistakes if I didn’t place the result in a pivot table like the one below.
As you can see above, this measure correctly identifies the first Month ID for each Policy regardless of what the Calendar table is doing.
Phasing % to Allocate
OK, next problem – I needed to be able to return the correct percentage to allocate in the same pivot table. The formula wasn’t hard to write as you can see below.
Month% = MAX(Phasing[Mix])
And here is the measure in the pivot table. Note that the measure returns the correct result regardless of the YYMM and the Pol Date. Note that in the Pivot table below I have placed the Phasing Month number from the Phasing table, not the YYMM from the calendar table. The Phasing[Month] means 1st month, second month etc.
Total Value of Policy
This one also wasn’t difficult to write either, but I needed to make sure it would give the correct answer in the pivot table I was using. I wrote a couple of versions before settling on this one. Originally I had a relationship between the Calendar table and the Policy table, so this one looked different early on. But once I removed the relationship I could use a simple aggregation function to grab the value.
Total Value = MAX(Policies[Amount])
The formula for the raw phasing was now pretty easy. Just multiply the Total Value of each policy by the percentage to allocate each month. Note again I am using the Phasing[Month] column, not the YYMM from the Calendar table. As you can see below, each policy is split into the 12 monthly buckets for the first month, second month and so on. Of course “which month” has not be resolved yet, nor has the aggregated total of all policies (the grand total row).
Push the Raw Phasing to the Correct Calendar Month
It took me a few attempts to move on from here. I actually tried to write the final formula to show the Raw Phasing amounts above without breaking the problem into pieces. I soon realised that I needed to take a step back and try to solve a different problem first. I needed to be able to create a measure that would show the phasing month (1 through 12) and have that phasing month appear in the correct place in the pivot table. It should be clearer when you look at the result below.
As you can see above, the first contract will be allocated the first month phasing in 1701, the second contract will be allocated the first month phasing in 1702 etc. It took me a few attempts to get this formula right too, and I ended up solving it in Excel first, then writing it in DAX. Sometimes solving logic in Excel can help.
Assigned Phasing Month = VAR InterimID = MAX('Calendar'[Month Index]) - [Pol First Month] + 1 RETURN IF(InterimID <= 12 && InterimID >= 1, InterimID)
To understand why I needed the IF statement, take a look at the pivot table without the IF statement below.
Actually, I probably could have kept the measure displaying as shown above, but it just felt “cleaner” to use the IF statement to clean it up.
Use the Assigned Phasing Month to assign the Future Flow
Note how I use the FILTER function in line 4 above to select the correct % to allocate to each month. If you refer back to the Phasing % to Allocate section earlier in this article, you will see that in that Pivot Table I placed the Month (1 through 12) in the test pivot table. This time I am using the Calendar table to create the Month (1 through 12) that should be used, and then I need to use this FILTER function to push that Month number onto the phasing table. This is the result. Note that the Grand Total is not working.
Iterate over the Policy Dates
Whenever the total doesn’t add up, I immediately reach for SUMX. The final formula I wrote and the resulting table is shown below.
Final = SUMX(VALUES(Policies[Pol Date]), [Future Flow])
Bringing It All Together
Once I had solved the problem, I decided to go back and combine the interim measures into a single consolidated measure that would be easier to manage and debug.
With the benefit of hindsight, I should have used variables to start with. I have attached my workbook here. => Phasing Income I would love to hear from anyone that has a more efficient solution.