We have accounting data table with transactions. The transaction data includes account, debit saldo and credit saldo. We also have account master data table, which includes account and type. The type defines whether the account is income account or expense account.

Income account increases in credit and decreases in debit. On the other hand, expense account increases in debit and decreases in credit.

Of course, it might happen that income account holds higher saldo in debit than in credit, but then the account is in negative side.

Reverse bookings are done when the first booking was erroneous. Therefore for income accounts debit postings must be deducted from credit postings and for expense postings credit postings from debit postings.

First we should calculate total income and then total expense.

The transaction data in Access.

The account master data table.

The simple data model.

The measures in Power BI.

tot_i = calculate(sum(Fill_z[C])-sum(Fill_z[D]), Acc_type[Type]=”I”)

tot_e = calculate(sum(Fill_z[D])-sum(Fill_z[C]), Acc_type[Type]=”E”)

The idea with measures is to CALCULATE function. First we need difference between debit and credit, or credit and debit. After the comma, comes the filtering parameter from Acc_type table. For income accounts, calculate sums of credits minus sums of debits. For expense accounts, calculate sums of debits minus sums of credits.

The results in Power BI.

The results can be checked in Access with SQL.

SELECT Sum(fill_z.c)-Sum(fill_z.d) AS Expr1

FROM fill_z

INNER JOIN acc_type ON fill_z.acc = acc_type.acc

WHERE acc_type.type = “I”

SELECT Sum(fill_z.d)-Sum(fill_z.c) AS Expr1

FROM fill_z

INNER JOIN acc_type ON fill_z.acc = acc_type.acc

WHERE acc_type.type = “E”