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.
It’s a good example of breaking apart a fairly complicated concept and rethinking equations as set operations in DAX.