I have a SQL source of investments, with a key. Each investment should produce monthly revenue immediately. Simplified, I have the columns InvestmentKey, MonthSinceInvestment (this starts at 0, increments by 1 each month), RevenueAmount and ExpectedRevenueAmount.
Due to merger & acquisition activity, I now have to integrate data from another company in this model. It's been loaded into SQL, but they do not always produce [RevenueAmount] or even [ExpectedRevenueAmount] immediately. Sometimes, their first [MonthSinceInvestment] value on an [InvestmentKey] will be 4, 5, 10, whatever.
In DAX, I do a IRR calculation using this value, and it relies on having a month 0, and 121 consecutive months through to Month 120 in [MonthSinceInvestment] for each [InvestmentKey]. Therefore, despite the new company not generating revenue until a few months after the investment, I must have values 0 through to 120 in [MonthSinceInvestment] for each and every [InvestmentKey]. It does not matter if no revenue is generated or expected, this actually makes the DAX calculation for IRR more accurate.
I need to identify all the [InvestmentKeys] that are missing a sequential value between 0 & 120 in [MonthSinceInvestment], and add rows to each of those [InvestmentKeys] to ensure that I have valid data to calculate on.
I have so far identified all the keys which are missing month 0, but I can't even figure out how to add the missing data to that. It's not a sufficient identification anyway. The steps I took to achieve this are:
#"Sorted Rows" = Table.Buffer(Table.Sort(Source,{{"InvestmentKey", Order.Ascending}, {"MonthSinceInvestment", Order.Ascending}})),
#"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"InvestmentKey"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each ([MonthSinceInvestment] <> 0))
Thanks in advance for any help, I hope I've explained enough to get started.
Edit: I have realised that if I can simply add the 121 rows for each key, I can use GroupBy to sum them into the revenue value, it will will leave all revenue values unchanged (because the data I'm adding will have null value), but gives me the rows I need. So really, the question is just, how to add 121 rows with values 0 to 120 for each key.
