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
3 months ago

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!