Sum of Last Balance with Missing Values

Flow variables are variables that represent the flow of something over a given period of time. In our case, the transaction represents the decrease or increase of the amount assigned to an account code over a time period, such as within a month. Transactions involve the movement of funds into or out of a bank account, and they occur over a certain time period.

Stock variables are variables that represent the total amount of something at a given point in time. In our example, the Balance measure is a state variable, because it describes the current condition on our account. It's a snapshot of the funds in the account at a particular moment. Stock variables are typically measured at a specific point in time, such as at the end of a month or a year.

Suppose that the stock variable values are stored in a Fact Table.

Suppose further that the stock variable is discriminated by one or more Dimensions. For example

• you have multiple cash accounts, each having a balance
• or you have stock levels of different articles

Further, suppose that the stock variable is measured at irregular intervals. For example

• typically we get one Cash Balance per month. But sometimes we get more than one per months. And sometimes, a month is missing.
• normally, we do inventory every quarter, but sometimes we skip it.

Finally, suppose that the missing values might be different from one dimension value to the other. For example

• for cash account A we get a balance on 31. Jan 2023 and 31 March 2023, whereas for cash account B we get a balance on 1. Jan 2023 and on 28. February 2023.
• or, inventory for warehouse A is made in January, and inventory for warehouse B is made in February.

Now, we would like to calculate our total balance, for any point in time.

Implicitly, we assume that "no news = no change". So, whenever we don't have new values of our stock variable, then the last value is still valid. Here, it is important to understand that this "fill-down" characteristic is per dimension.

We would now like a DAX measure that calculates, for any point in time, the sum of the stock variable's level, taking into account the last value of the stock variable, per category.

Example

You can download a pbix with the data here:

We are looking at balances of bank accounts.

We have a very simple Star-Schema:

Let's say the data in our fact table looks like this:

In an unpivoted view, we have:

If we fill this down, by account, we get:

We can then easily calculate the total cash balance for any date by summing over the accounts:

We would like to calculate and display the column in Power BI.Total

Solution

We get to the solution in a few steps:

1. Index: we add an index column in Power Query to order our transactions

2. Transaction: this is a calculated column that shows the change of amounts between months (increase or decrease), per account

3. Balance: this measure sums up the transactions between periods

Transaction

Transaction = 


 Adds a calculated column that calculates the transactions (flow variable) from the balances (stock variable) 
    VAR _cur_idx = 'FactAmount'[Index]

 VAR _balances_for_account = CALCULATETABLE('FactAmount', ALLEXCEPT('FactAmount', 'FactAmount'[Account]) ) 
    VAR _before_date = FILTER ( _balances_for_account, [Index] < _cur_idx )

 VAR _last_balance_tbl = TOPN ( 1, _before_date, [index] ) 
 VAR _last_balance = SELECTCOLUMNS( _last_balance_tbl, "@Balance", [Amount] ) 
    VAR _transaction = FactAmount[Amount] - _last_balance


    VAR _trx = IF(FactAmount[AccountType] = "T", FactAmount[Amount], _transaction)


RETURN

    _trx

With this column we get the increase or decrease that happened between two transactions per AccountCode:

Nice!

Balance

Balance = 

 calculates the balance as the sum of all previous (calculated) transactions, by account 
    var _max_date = LASTDATE(DimCalendar[Date])   

    var _amnt = CALCULATE( SUM(FactAmount[Transaction]), REMOVEFILTERS(DimCalendar), KEEPFILTERS(DimCalendar[Date] <= _max_date))

    return _amnt

The trick lies in the combination of `REMOVEFILTERS` and `KEEPFILTERS`. So, when the filter context is, say, February 2023, the filter context is changed to any date before the last date of February.

In a nutshell, before we derived the transactions (flow variable) from the balance. Now, we calculate again the balance (state variable) from the derived transactions.

For our example, this yields:

Aggregation

As handling flow variables is much easier than handling state variables, the new measure for balance can be summed up.

Filtering

So far, we haven't filtered for any account. And in the example of the G/L ADM, we have lots of accounts, whereby only few are cash accounts.

Therefore, we typically need to filter.

We do this by using calculate on the 'Balance' measure.

For example, we can create a 'Cash' measure like this:

Cash = CALCULATE([Balance], DimAccount[AccountMainGroupCode] = 10)

We could easily add more filters if needed, e.g.

Cash = CALCULATE([Balance], DimAmountType[AmountTypeCode] = "B", DimAccount[AccountMainGroupCode] = 10)

Final Thoughts

DAX is powerful. But sometimes it is so hard.

One would think the requirements described in this article are common. Though googling will not help.

Building these DAX measures is also surprisingly hard.

For these reasons, we at Power Partners build a library of patterns. Let us know if you have similar patterns that might be helpful to the community!