Forum Discussion

liveincolorado's avatar
liveincolorado
Regular Visitor
4 months ago
Solved

Inferring Missing Columns and Imputing Missing Data

I am in the analysis stage of creating a PBI Report.  Here is what I am working with (not final...just preliminary  steps): Tabular Editor / The Cube Oracle SQL Developer Power BI Desktop Excel...
  • v-sgandrathi's avatar
    v-sgandrathi
    4 months ago

    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.

liveincolorado's avatar
liveincolorado
Regular Visitor
4 months ago

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

  • liveincolorado's avatar
    liveincolorado
    Regular Visitor
    4 months ago

    Unfortunately, this produces the same error as my earlier attempts.  It is not counting the total active accounts in a month.  

    • v-sgandrathi's avatar
      v-sgandrathi
      Community Support
      4 months ago

      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.

      • v-sgandrathi's avatar
        v-sgandrathi
        Community Support
        4 months ago

        Hi liveincolorado,

         

        We haven’t heard from you on the last response and was just checking back to see if your query was answered.
        Otherwise, will respond back with the more details and we will try to help.

        Thank you.