Balance sheet question. Crossjoin solution?

[Anonymous]

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 means that if I get the balance for the end of march - july wrong. 

I guess I cannot do anything about what I get from ERP so I have to "DAX" myself out of this one to be able to present it like the table on the right. 

I was thinking of a new table that would be a crossjoin between my date table and the chartered accounts and to do make some kind of DAX formula to pick the previous month Out_Bal if 0-ish.

How do I do this crossjoin and how do I relate this new table in my model? Or is there any other solutions?

Regards!

[Aryna]

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/ 

[PaulDBrown · Accepted solution]

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]

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]

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)

[Anonymous]

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.