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.
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
;