CODEWITHSUNDEEP
powerbidax
Jelle Hoekstra
Jelle Hoekstra

Reputation: 672

Balance sheet measure DAX (fill blanks)

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.

enter image description here

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

Upvotes: 1

Views: 908

Answers (1)

RADO
RADO

Reputation: 8148

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

enter image description here

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:

enter image description here

Upvotes: 1

Related Questions