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.
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.
Thank you. It is too heavy a lift for the report, so we are going back to sql statements. There is one for the accounts (with start and end dates) and then one for balances that aggregates "total this month" as
SUM(month_total_balance) OVER (PARTITION BY acct_id
ORDER BY monthly_balance) as monthly total
I can work with this in PBI.Hi liveincolorado,
Thanks for the update. Moving the logic to SQL is a good idea when Power BI transformations get complex, especially with account and month expansions.
Using window functions for monthly totals per account is a solid approach. You could also create a monthly snapshot in SQL with active account flags and balances, so Power BI can use the dataset directly.
With the data shaped at the source, Power BI can handle slicing, aggregation, and visualization more efficiently. If you have any issues modeling this, let me know, I’m happy to help.
Thank you.
