Balance sheet measure DAX (fill blanks)

Question

Background I'm trying to build a balance sheet in Power BI based on a transaction file. My report has a transaction table containing classic accounting transactions (account number, amount, description, date etc.), an allocation table which allocates accounts to a balance sheet, P&L or cashflow hierarchy (account, PLlvl1, PLlvl2 etc.) and a calendar table.

Constructing a proper running total measure to sum all previous transactions creating a basic balance measure is pretty straight forward, see code below.

Balance =     
    CALCULATE( 
        SUM ( data[Amount] ) ; 
        FILTER(
            ALL( '$Calendar' );
            '$Calendar'[Date] <= MAX( '$Calendar'[Date] )
        )
    )

Problem This works fine at low resolutions (year) however, when making a month on month overview, the summation only show a value in periods where there was a mutation, all other months remain empty.

Desired solution In this simplified example, my desired result would be for the the blanks to carry over values from the previous period, the -350 also showing in February and March, the -700 in May and June etc. etc. but I cant seem to figure a way to do it properly.

Attempts So far I've tried creating a huge cross table between the calendar table and the accounts table but this makes the report grind to a halt pretty fast as soon as I import more data. Furthermore I tried using LASTNONBLANK(), TOTALYTD() and others in several ways even trying a more manual approach like:

Attempt 6 = 
var LastNonBlankDate=
    CALCULATE(
        MAX('$Calendar'[Date]);
        FILTER(
            ALL('$Calendar'[Date]);
            '$Calendar'[Date]<=MAX('$Calendar'[Date]) && SUM(data[Amount])<>0)
        )
RETURN
    CALCULATE(SUM(data[Amount]);
    FILTER(ALL('$Calendar');'$Calendar'[Date]=LastNonBlankDate))

Nothing seems to do what I want..

Can somebody help me in the right direction?

A fiddle is temporary available here

RADO RADO · Accepted Answer · 2020-02-19T23:37:29

Just change your data model relations into "single" from "both":

Never, ever use bi-directional relations unless you have no other choice (which almost never happens except some very rare situations, which is not the case here).

You can also simplify your measure a bit:

Attempt 3 = 
VAR Current_Date = MAX( '$Calendar'[Date] )
RETURN
    CALCULATE( 
        SUM ( data[Amount] ) , 
        '$Calendar'[Date] <= Current_Date
    )

Result:

Balance sheet measure DAX (fill blanks)

1 Answers