Inferring Missing Columns and Imputing Missing Data

Hi liveincolorado,

I've created the sample .pbix file for you with DAX for all the three columns.

Active Accounts = 
VAR StartDate = MIN('Date'[Date])
VAR EndDate = MAX('Date'[Date])
RETURN
CALCULATE(
    DISTINCTCOUNT(Accounts[ACCT_ID]),
    FILTER(
        Accounts,
        
        -- CLI condition
        Accounts[CLI_START_DT] <= EndDate &&
        COALESCE(Accounts[CLI_END_DT], DATE(9999,12,31)) >= StartDate &&

        -- SA condition
        Accounts[SA_START_DT] <= EndDate &&
        COALESCE(Accounts[SA_END_DT], DATE(9999,12,31)) >= StartDate
    )
)

Total Balance = 
VAR EndDate = MAX('Date'[Date])
RETURN
SUMX(
    VALUES(Accounts[ACCT_ID]),
    
    VAR LatestRow =
        CALCULATE(
            MAX(Accounts[CLI_START_DT]),
            FILTER(
                Accounts,
                Accounts[ACCT_ID] = EARLIER(Accounts[ACCT_ID]) &&
                Accounts[CLI_START_DT] <= EndDate
            )
        )

    VAR Balance =
        CALCULATE(
            MAX(Accounts[TOTAL_BALANCE]),
            Accounts[CLI_START_DT] = LatestRow
        )

    VAR IsActive =
        CALCULATE(
            COUNTROWS(Accounts),
            FILTER(
                Accounts,
                Accounts[ACCT_ID] = EARLIER(Accounts[ACCT_ID]) &&
                Accounts[CLI_START_DT] <= EndDate &&
                COALESCE(Accounts[CLI_END_DT], DATE(9999,12,31)) >= EndDate &&
                Accounts[SA_START_DT] <= EndDate &&
                COALESCE(Accounts[SA_END_DT], DATE(9999,12,31)) >= EndDate
            )
        )

    RETURN IF(IsActive > 0, Balance)
)

Avg Payoff Balance = 
DIVIDE(
    [Total Balance],
    [Active Accounts]
)

Please see attached file.

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

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

Hi liveincolorado,

Thank you for sharing your Power Query solution, it's a thorough approach, and you've clearly considered the monthly expansion and carry-forward logic. However, I recommend caution, as creating a full account × month cross join and multiple transformations can result in a large dataset and may affect performance as your data grows. This could also increase model complexity.

In Power BI, it's typically more efficient to handle this logic in DAX measures rather than in Power Query. The DAX method dynamically evaluates active accounts by date context, manages open-ended dates with COALESCE, and retrieves the latest balance without generating all month combinations.

Your Power Query method is valid if you require a precomputed snapshot table, but it's best to limit the date range and avoid heavy pivoting to keep the model manageable. I suggest testing both approaches with your dataset to compare performance, refresh time, and usability before making a final decision.

Thank you.

Hi liveincolorado,

Thank you for the update.

It's great that you found a more efficient way by managing part of the logic in SQL, as that should help with performance.

If you need any more help with the data in Power BI, please let me know.

Thanks again.

Need to rethink the goal of the report.  The focus is aggregated yearly stats with no need (at least for now) for any drill-through.  The SQL will be time-bound (never ideal), because the full dataset is 5gb.  Since the focus is on more recent data (after 2023), but access to more historical data (before 2023), then data refreshes for data before 2023 can be quarterly and data refreshes for 2023 -2025 can be monthly, and data for 2026 can be daily.  The following example is being tested for each year going back 10 years:

--ACCOUNTS
WITH ACTIVE AS (
SELECT DISTINCT

a.acct_id,

a.a_start,

a.a_end,

b.b_start,

b.b_end,

FROM accounts a

LEFT JOIN status b

ON a.acct_id = b.acct_id

WHERE a.type = 'current'

AND a.a_start <= DATE '2025-12-31'

AND (a.a_end >= DATE '2025-01-31'

)

SELECT * FROM (

SELECT

active.acct_id,

active.a_start,

active.a_end,

active.b_start,

active b_end,

/* FLAG 1 */
CASE WHEN EXISTS (

SELECT 1 FROM accounts a

WHERE a.type = 'current'

AND a.acct_id = active.acct_id

AND a.a_start <= DATE '2025-01-31'

AND (a.a_end >= DATE '2025-01-01 OR a.a_end IS NULL)
) THEN 'Yes' ELSE 'No' END AS active1_jan_2025

/ * REPEAT FOR EACH MONTH REMAINING: FEB - DEC */

/* FLAG 2 */

CASE WHEN EXISTS (

SELECT 1 FROM accounts a

WHERE a.type = 'current'

AND a.acct_id = active.acct_id

AND b.b_start <= DATE '2025-01-31'

AND b.b_end >= DATE '2025-01-01 OR b.b_end IS NULL)
) THEN 'Yes' ELSE 'No' END AS active2_jan_2025

/ * REPEAT FOR EACH MONTH REMAINING: FEB - DEC */

FROM ACTIVE

)

;

--BALANCES:

WITH ACCOUNTS AS (
SELECT DISTINCT a.acct_id
FROM accounts.account a
WHERE a.type = 'current'
AND a.start <= '2025-12-31'
AND (a.end >= '2025-01-01 OR a.end IS NULL)
),
JANBAL AS (
SELECT
b.acct_id,
SUM(total) AS total_jan_2025,
SUM(current) AS current_jan_2025
FROM amounts b
JOIN transactions c ON b.id = c.id
WHERE TRYN(c.create) <= DATE '2025-01-31'
GROUP BY b.acct_id

/* REPEAT FOR EACH MONTH REMAINING: FEB - DEC */

)

SELECT
acct_id,
janbal.tot_jan_2025,
janbal.cur_jan_2025,
/* REPEAT FOR EACH MONTH REMAINING: FEB - DEC */

FROM ACCOUNTS
LEFT JOIN JANBAL USING (ACCT_ID)
/* REPEAT FOR EACH MONTH REMAINING: FEB - DEC */

ORDER BY acct_id
;

Hi liveincolorado,

You’re on the right track by simplifying the goal to aggregated yearly stats and using tiered refresh logic, which is a solid way to handle a 5GB dataset.

However, the current SQL approach, creating monthly flags like active1_jan_2025 and repeating logic for each month and year will be hard to maintain and scale, especially over more than 10 years.

Instead of creating separate columns for each month, consider restructuring the output to return data at a month grain (row-based), such as acct_id, year, month, is_active, and total_balance. This method avoids repeating SQL for every month and keeps the dataset flexible.

For your refresh strategy, your idea of quarterly/monthly/daily splits fits well with incremental refresh or partitioning. You can still use time-bound SQL, but it’s better to parameterize it by year or date range rather than hardcoding each month.

Your use of window functions for balances is effective. You can expand this to create a monthly snapshot table (one row per account per month), which Power BI can easily aggregate to the yearly level.

Thank you.

This thread morphed into something that was difficult to follow.  The noted solution in this post is not correct for me; it did not work.  The solution is ~950 line SQL statement that aggregates a series of linear steps and formulas in Excel, into one SQL statement.  The final output for a single year is a stand-alone dataset of 12 rows with the following columns:

Month
No of Active Accounts
Total Balance
Avg Monthly Balance of Accounts

The reason for this approach was processing load and row limits in Tabular Editor along with two different grains that the stakeholder's final Excel workbook manually created.  Historical years up to 2025 are not refreshed; 2026 is refreshed daily.  Each year the report will need maintenance.  (Until we have more compute and higher allowable rowcounts, this is the solution that works.)

From this one-SQL solution, only 3 measures are needed for other visuals on the report page:

Anonymized DAX is: 

xxxx Avg Account Count =
AVERAGE(‘PROJECT_BALANCES'[# Active Accounts])

xxxx Avg of Avg Payoff Balance per Account =

AVERAGE(‘PROJECT_BALANCES'[Average Payoff Balance per Account])

xxxx Avg Total Payoff Balance =
AVERAGE(‘PROJECT_BALANCES'[Total Payoff Balance])

I appreciate the feedback.  If you want the SQL, dm me.

Thanks!