Accounting Archives – The BIccountant

Dynamic Benford’s Law measures in Power BI and Power Pivot

Benford’s Law compares the frequency distribution of leading digits to its (empirically proven) natural counterpart. This can then be used to detect fraud and errors.

Comparison between Benford distribution and actual

The green columns show how often each number should be the first digit in numbers that should follow the Benford-distribution. In black you’ll see the actual distribution of first digits within my table. Lastly, the red line shows the percentual absolute deviations between actual and Benford values.

In this example, there is a relatively high occurrence of numbers starting with 4 and 5. So this could be a sign for fraudulent manipulations.

The Benford Distribution

First you need a table with the Benford-distribution. Just load it as a disconnected table to your data model and name it “BenfordTable”. The “Value”-field from this table will be taken as x-axis for the visualisations. As the Benford-distribution is logarithmic, it can quickly be created with the following DAX-code:

Benford’s law Measure

The measure calculates how often a number starts with one of the BenfordNumbers (1..9) compared to the total number of rows in the FactTable.

If there are blank in the Value-column of the table to be analyzed, the measure has to be adjusted by filtering them out in the VAR CountTotal: ( CALCULATE(COUNTROWS(FactTable), FactTable[Value] <> BLANK()) )

Please note that you can create as many measures as you need in one model. So if you have multiple columns to investigate, just write a measure for each.

Benford’s law Variance Measure

To calculate the difference between target and actual, I use a MAXX-aggregation. This returns the maximum difference there is for a number. I also use this in a card visual if I want to add a data driven alert. So I don’t have to check and eyeball the chart regularly, but can just use this in a card visual in a dashboard. Then I’ll set a threshold value for the alert and will not miss any alarming developments.

Why DAX?

I’m using measures here instead of a calculated column (in the Benford-Table) because this allows me to filter and slice my table. This allows for making advanced and flexible analysis like comparing different values against each other or over time against the Benford distribution:

Benford’s Law Charts: Comparisons with various dimensions

Enjoy & stay queryious 😉

Parent-Child Hierarchies with multiple parents in Power BI with Power Query

I’ve written about a method to dynamically flatten parent-child-hierarchies also with multiple parents some while ago here. I’ve actually used this approach for Bill-of-materials cases and refined that approach in a series starting here. There, the quantities are aggregated in M already, as they are not supposed to change. But if one wants to use the hierarchical structure to report on transaction tables where several filters shall be applied, one has to adjust the data model a bit:

DimNodes

If you have parent-child-hierarchy with multiple parents, my function will a table like below, where the children with multiple parents still reside in different rows:

Automatically validate E-mail attachments with Flow and Power BI

Receiving files from business partners that don’t match the agreed requirements/formats causes all sorts of problems in daily business. I found it particularly disturbing during month-end closing when time is really tight: You have a strict rule in which order each process has to run and there are many dependencies between them. So when then one import doesn’t work, many other processes will come to a halt as well. Fortunately, today there is a simple remedy for it: Automatically validate E-mail attachments with Flow and Power BI

Process Automation with Flow and Power BI

You can create a Flow that “listens” for incoming emails in a mailbox that match certain criteria and contain attachments. Flow can then extract these attachments and save them to an online-folder. After that, Flow triggers a refresh of a Power BI dataset, that imports these attachments and checks for the data quality-criteria that you have defined. Then you create measures for the data quality that trigger data driven alerts from Power BI service. Flow then listens for these alerts and sends an email back to the sender, requesting for a corrected file.

This not just saves crucial time, but also your nerves (and those of your team-mates).

The Details

Get full Time Activity data from QuickBooks into Power BI

Problem

As per the time of writing, the native QuickBooks connector in Power BI has some shortcomings for the Time Activity-data: It will not return employee details (so you will not know who did the hours) and it will not return hours (if they haven’t been entered by start- and end-date).

Solution

But fortunately the connector has 2 functions, who can return the full data that the QBO-API has to offer. At the end of the list in the navigation pane there are the functions “Entity” and “Report”:

Export data from Power BI to csv using Python

In this blogpost I show you my M-Python-function that I use to export data from Power BI to csv files (Export Python).

Why Python?

I prefer it to R mostly because I don’t have to create the csv-file(names) in advance before I import data to it. This is particularly important for scenarios where I want to append data to an existing file. The key for this task is NOT to use the append-option that Python offers, because M-scripts will be executed multiple times and this would create a total mess in my file. Instead I create a new file with the context to append and use the Import-from-folder method instead to stitch all csvs back together. Therefore I have to dynamically create new filenames for each import. So when the M-Python-scripts are executed repetitively here, the newly created file will just be overwritten – which doesn’t do any harm.

Why Power BI isn’t suitable for Financial Statements (… currently … hopefully …)

!! This is a clickbait post to get your vote for some missing features in Power BI !!

Although this might not be what the inventors of Power BI had in mind, large lots of folks are trying to create classical financial statements in it. And putting aside the afford that might go into getting the numbers right, there is still a major drawback to swallow:

New Generation Finance – Accounting – Controlling using Microsoft BI stack

Posts in Category ‘Accounting’