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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Here is an edit to my question: this is rather what the data looks like from the ERP. No change = no rows. And since I have no row I can't write a DAX formula that picks previous month or someting if empty.