This blog post is an extension to my earlier blog post about accounting data in BI reporting.

Here is some accounting data. Accounts starting with three are income accounts increasing value in credit and accounts starting with four cost accounts increasing value in debit side.

The dimensions are account, cost center, date, credit value and debit value.

It would be useful to see profit and loss dynamically per cost center or per time dimension like month.

Create sum measure to count debit balance.

Summing the credit side.

These measures do not take yet correction postings into consideration. Account 3000 should be 2282 minus 250.

Sum debit total is sum debit minus sum credit.

Sum credit total is sum credit minus sum debit.

Now the balance for 3000 is 2032 in credit, but -2032 in debit should be removed.

Create an if sentence. IF sentence in DAX is pretty similar as in traditional Excel. If sum debit minus sum credit is positive, then count normally sum debit minus sum credit. If sum debit minus sum credit is negative, then the formula returns zero value. Separator is comma not semicolon.

The same operation for credit side as for the debit.

Now the results are as they should be.

The selections.

Adding the cost center.

This looks better but cost center totals are missing.

Then select subtotals – show all subtotals at bottom of group.

The highest profit was made in cost center S2.

Same measures can be created to BI also.

For me Excel work better in this case.