Forum Discussion
Inferring Missing Columns and Imputing Missing Data
Hi liveincolorado,
Thank you for the update.
Total Balance =
VAR MonthStart = MIN('Date'[Date])
VAR MonthEnd = MAX('Date'[Date])RETURN
SUMX(
VALUES(Accounts[ACCT_ID]),VAR LatestDate =
CALCULATE(
MAX(Accounts[CLI_START_DT]),
FILTER(
Accounts,
Accounts[ACCT_ID] = EARLIER(Accounts[ACCT_ID]) &&
Accounts[CLI_START_DT] <= MonthEnd
)
)VAR Balance =
CALCULATE(
MAX(Accounts[TOTAL_BALANCE]),
Accounts[CLI_START_DT] = LatestDate
)VAR IsActive =
CALCULATE(
COUNTROWS(Accounts),
FILTER(
Accounts,
Accounts[ACCT_ID] = EARLIER(Accounts[ACCT_ID]) &&
Accounts[CLI_START_DT] <= MonthEnd &&
COALESCE(Accounts[CLI_END_DT], DATE(9999,12,31)) >= MonthStart &&
Accounts[SA_START_DT] <= MonthEnd &&
COALESCE(Accounts[SA_END_DT], DATE(9999,12,31)) >= MonthStart
)
)RETURN
IF(IsActive > 0, Balance)
)Thankyou.
Thank you! I'll let you know how this works. I've been playing with this:
let // Replace this with your actual source table step Source = YourSourceTableHere, // Ensure types TypeFixed = Table.TransformColumnTypes(Source, {{"acct_id", type text}, {"cli_start_dt", type date}, {"cli_end_dt", type nullable date}, {"sa_start_dt", type date}, {"sa_end_dt", type nullable date}, {"total_balance", type nullable number}, {"current_balance", type nullable number}}),
// Window parameters: set first month and number of months StartMonth = #date(2016, 4, 1), // set to desired first month (first day) Months = 123,
// Build global month list for the window MonthList = List.Transform({0..(Months-1)}, each Date.AddMonths(StartMonth, _)), MonthTable = Table.FromList(MonthList, Splitter.SplitByNothing(), {"MonthStart"}), MonthTable2 = Table.AddColumn(MonthTable, "YearMonth", each Date.ToText([MonthStart],"yyyy-MM")),
// Normalize null ends to a far future so open-ended accounts are treated as active if in window NormEnds = Table.TransformColumns(TypeFixed, {{"cli_end_dt", each if _ = null then #date(9999,12,31) else _, type date}, {"sa_end_dt", each if _ = null then #date(9999,12,31) else _, type date}}),
// Cross join accounts with month table => account x month (full window) AddKeysrc=Table.AddColumn(NormEnds, "Key", each 1), AddKeyMonths = Table.AddColumn(MonthTable2, "Key", each 1), Join = Table.NestedJoin(AddKeySrc, "Key", AddKeyMonths, "Key", "M", JoinKind.Inner), ExpandMonths = Table.ExpandTableColumn(Join, "M", {"MonthStart","YearMonth"}, {"MonthStart","YearMonth"}),
// Compute month end for comparisons AddMonthEnd = Table.AddColumn(ExpandMonths, "MonthEnd", each Date.EndOfMonth([MonthStart]), type date),
// Flags per month AddCLIFlag = Table.AddColumn(AddMonthEnd, "CLI_In_Month", each ([cli_start_dt] <= [MonthEnd] and [cli_end_dt] >= [MonthStart]), type logical), AddSAFlag = Table.AddColumn(AddCLIFlag, "SA_In_Month", each ([sa_start_dt] <= [MonthEnd] and [sa_end_dt] >= [MonthStart]), type logical), AddActive = Table.AddColumn(AddSAFlag, "IsActive", each ([CLI_In_Month] and [SA_In_Month]), type logical),
// Sort and carry-forward balances per acct across months (fill down) Sorted = Table.Sort(AddActive, {{"acct_id", Order.Ascending}, {"MonthStart", Order.Ascending}}), KeepCols = Table.SelectColumns(Sorted, {"acct_id","MonthStart","YearMonth","MonthEnd","CLI_In_Month","SA_In_Month","IsActive","total_balance","current_balance"}), Grouped = Table.Group(KeepCols, {"acct_id"}, {{"Rows", each Table.FillDown(Table.TransformColumnTypes(_, {{"total_balance", type nullable number}, {"current_balance", type nullable number}}), {"total_balance","current_balance"})}}), Expanded = Table.ExpandTableColumn(Grouped, "Rows", {"MonthStart","YearMonth","MonthEnd","CLI_In_Month","SA_In_Month","IsActive","total_balance","current_balance"}),
// Replace remaining null balances (leading nulls) with 0 ReplaceNulls = Table.ReplaceValue(Expanded, null, 0, Replacer.ReplaceValue, {"total_balance","current_balance"}),
// Pivot each measure into month-columns. We'll create three pivoted tables and join them back. SelectBase = Table.SelectColumns(ReplaceNulls, {"acct_id","YearMonth","CLI_In_Month","SA_In_Month","IsActive","total_balance","current_balance"}),
// Pivot CLI CLI = Table.SelectColumns(SelectBase, {"acct_id","YearMonth","CLI_In_Month"}), CLI_Pivot = Table.Pivot(Table.TransformColumnTypes(CLI, {{"YearMonth", type text}}), List.Distinct(CLI[YearMonth]), "YearMonth", "CLI_In_Month", List.Max),
// Pivot SA SA = Table.SelectColumns(SelectBase, {"acct_id","YearMonth","SA_In_Month"}), SA_Pivot = Table.Pivot(Table.TransformColumnTypes(SA, {{"YearMonth", type text}}), List.Distinct(SA[YearMonth]), "YearMonth", "SA_In_Month", List.Max),
// Pivot IsActive ACT = Table.SelectColumns(SelectBase, {"acct_id","YearMonth","IsActive"}), ACT_Pivot = Table.Pivot(Table.TransformColumnTypes(ACT, {{"YearMonth", type text}}), List.Distinct(ACT[YearMonth]), "YearMonth", "IsActive", List.Max),
// Pivot total_balance TB = Table.SelectColumns(SelectBase, {"acct_id","YearMonth","total_balance"}), TB_Pivot = Table.Pivot(Table.TransformColumnTypes(TB, {{"YearMonth", type text}}), List.Distinct(TB[YearMonth]), "YearMonth", "total_balance", List.Max),
// Pivot current_balance CB = Table.SelectColumns(SelectBase, {"acct_id","YearMonth","current_balance"}), CB_Pivot = Table.Pivot(Table.TransformColumnTypes(CB, {{"YearMonth", type text}}), List.Distinct(CB[YearMonth]), "YearMonth", "current_balance", List.Max),
// Merge all pivot tables on acct_id Merge1 = Table.NestedJoin(CLI_Pivot, "acct_id", SA_Pivot, "acct_id", "SA", JoinKind.LeftOuter), Merge2 = Table.ExpandTableColumn(Merge1, "SA", Table.ColumnNames(SA_Pivot)), Merge3 = Table.NestedJoin(Merge2, "acct_id", ACT_Pivot, "acct_id", "ACT", JoinKind.LeftOuter), Merge4 = Table.ExpandTableColumn(Merge3, "ACT", Table.ColumnNames(ACT_Pivot)), Merge5 = Table.NestedJoin(Merge4, "acct_id", TB_Pivot, "acct_id", "TB", JoinKind.LeftOuter), Merge6 = Table.ExpandTableColumn(Merge5, "TB", Table.ColumnNames(TB_Pivot)), Merge7 = Table.NestedJoin(Merge6, "acct_id", CB_Pivot, "acct_id", "CB", JoinKind.LeftOuter), Final = Table.ExpandTableColumn(Merge7, "CB", Table.ColumnNames(CB_Pivot)) in Final