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.

v-sgandrathi's avatar
v-sgandrathi
Community Support
3 months ago

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.