Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Balance sheet question. Crossjoin solution?

I am struggling with balance sheet report in PBI.    When extracting the data from our ERP I get the data as below to the left. As you can see I don't get a row if there isn't any changes which mea...
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    3 years ago

    Here is one way.

    First the model:

     With simple SUM measures for each value, and then:

    New In_Bal =
VAR _MaxDate =
    CALCULATE (
        MAX ( ERP[Date] ),
        FILTER (
            ALLEXCEPT ( ERP, 'Account Table'[Account] ),
            NOT ISBLANK ( [Sum In Bal] )
        )
    )
RETURN
    SWITCH (
        TRUE (),
        MAX ( 'Date'[Date] ) <= _MaxDate, [Sum In Bal],
        CALCULATE ( [Sum  Out_bal], FILTER ( ALL ( 'Date' ), 'Date'[Date] = _MaxDate ) )
    )

    New Out_Bal =
VAR _MaxDate =
    CALCULATE (
        MAX ( ERP[Date] ),
        FILTER (
            ALLEXCEPT ( ERP, 'Account Table'[Account] ),
            NOT ISBLANK ( [Sum  Out_bal] )
        )
    )
RETURN
    SWITCH (
        TRUE (),
        MAX ( 'Date'[Date] ) <= _MaxDate, [Sum  Out_bal],
        [New In_Bal]
    )

    To get:

     Sample PBIX attached

Aryna's avatar
Aryna
Post Partisan
4 months ago

Hi, one option is to avoid building that balance sheet logic from scratch with a crossjoin table unless you really need to. A prebuilt Balance Sheet template can save a lot of time here, especially for handling month-end balances, missing periods, and comparison logic in Power BI.

 

This one may be worth checking out: https://vidi-corp.com/free-quickbooks-power-bi-template/