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

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

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.