Power Bi, Dax - calculations, filters, balance

Question

Could you please help me to solve the problem as I am totally new to DAX and English is not my first language so I am struggling to even find the correct question.

Here's the problem.

I have two tables:

start_balance

+------+---------------+
| Type | Start balance |
+------+---------------+
| A    |             0 |
| B    |            10 |
+------+---------------+

in_out

+------+-------+------+----+-----+
| Year | Month | Type | In | Out |
+------+-------+------+----+-----+
| 2020 |     1 | A    | 20 |  20 |
| 2020 |     1 | A    |  0 |  10 |
| 2020 |     2 | B    | 20 |   0 |
| 2020 |     2 | B    | 20 |  10 |
+------+-------+------+----+-----+

I'd like to get the result as follows:

Unfiltered:

+------+-------+------+---------+----+-----+------+
| Year | Month | Type | Balance | In | Out | Left |
+------+-------+------+---------+----+-----+------+
| 2020 |     1 | A    |       0 | 20 |  20 |    0 |
| 2020 |     1 | B    |      10 | 20 |  10 |   20 |
| 2020 |     2 | A    |       0 | 20 |  10 |   10 |
| 2020 |     2 | B    |      20 | 20 |  10 |   30 |
+------+-------+------+---------+----+-----+------+

Filtered (for example year/month 2020/2):

+------+-------+------+---------+----+-----+------+
| Year | Month | Type | Balance | In | Out | Left |
+------+-------+------+---------+----+-----+------+
| 2020 |     2 | A    |       0 | 20 |  10 |   10 |
| 2020 |     2 | B    |      20 | 20 |  10 |   30 |
+------+-------+------+---------+----+-----+------+

So while selecting a slicer for the year/month it should calculate balance before selected year/month and then show selected year/month values.

Edit: corrected start_balance table.

msta42a msta42a · Accepted Answer · 2020-10-01T12:25:32

Is the sample data correct?

A -> the starting balance is 10, but in your unfiltered table example, it is 0.

Do you have any relationship between these tables? Does opening balance always apply to the current year? What if 2021 appears in the in_out table? How do you know when the start balance started?

example without starting balance

If you want to show value breaking given filter you should use statement ALL or REMOVEFILTERS function (in Analysis Services 2019 and in Power BI since October 2019).

calculate(sum([in]) - sum([out]), all('in_out'[Year],'in_out'[Month]))

More helpful information: https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept/

Power Bi, Dax - calculations, filters, balance

1 Answers