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

PaulDBrown's avatar
PaulDBrown
Community Champion
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

    Thank you very much! We're getting closer but not really there. This is how i it looks if I create your measures in my model:

     

    There seems to be two problems:

    1. You maxdate might not work when more than oce account is shown. It works (with one exception, see 2.) fine when I've filtered so only one account is shown.

    2. I does not handle "gaps" very well. Both when many accounts are shown but also when only one is.

     

     

    • PaulDBrown's avatar
      PaulDBrown
      Community Champion
      3 years ago

      The measures work as posted, with the model structure posted, work with more than one account (I actually added one to check) and deliver the expected outcome you posted as an example (I've added an end of month field to the date table to replicate the exact structure you posted)

       The question therefore is how is your model set up? What is the structure of the visual? (Since it isn't the same as the table visual you posted as your expected outcome)