In this blog I present how to report accounting data in Power Pivot and Power BI.

Accounts 500 and 501 are revenue accounts and they get increased in credit side. However, some mistakes were corrected with debit postings.

When reporting the revenue, the debits should be deducted from credit.

The total revenue is 380.

However, if you want to report dynamically by month or by account, it’s better to call up Power Pivot and Power BI.

Get the data to the Power Query.

Transform data.

As the date is in Finnish format, take options and file – settings – query options.

Change regional settings to Finnish.

Change data type for date column as date. Data type for account was changed to text as we have no plans to execute any calculations based on account number. Debit and credit should be number type because they are used in measures.

File – Close & Load to…

Only create connection and add this data to the data model.

A measure to sum up the debit postings.

Another measure for the credits.

The total measure to count the net postings, meaning credits minus debits.

Select totals per account.

The results in Excel.

Let’s do the same in Power BI, in case you prefer using Power BI.

Get data – CSV.

Transform data.

Change data type for account as text. No plans to use account as numeric data.

Add calculated column Date – month – month.

Close & Apply.

Then we create measure just like in Power Pivot.

Sum for debit side.

Sum for credit side.

Totals are counted credit minus debit as revenue increases in credit side.

The results are the same as in Excel. Power BI is presenting the results more graphically than Excel.