DAX

Automatically plug or balance your balance sheet in Power BI

by

This article presents a method to automatically plug or balance your balance sheet in Power BI. You can use it if your bookkeeping system doesn’t contain the Profit and Loss (PnL) bookings yet. Or for planning or financial modelling purposes when your calculated balance sheet figures don’t add up. The problem to plug or balance … Read more

Forecasting with seasonality in Power BI

by

Today I’m starting a small series about forecasting in Power BI. In this blogpost I’ll start with a basic example about forecasting with seasonality that I will build upon in the following posts. Please note, that the monthly variation I am using in this example is taken from an existing table and not derived by … Read more

Calculating doubling times with DAX in Power BI

by

In this article I show methods to calculate the doubling time with DAX in Power BI. Doubling time is an indicator used for exponential growth scenarios. It indicates how much time it takes for a figure to double.

You might have come across it in studies covering the current COVID-19 epidemy like here for example. In there you see how many days it took for cases to double. But these figures are shown as snapshot of today and I think it’s also helpful to see their development over time. With a bit of DAX we’ll get there:

Doubling time in DAX (using small multiple visual from Daniel Marsh-Patrick)

Low values mean high speed of growth, so the bottom area is the danger zone here. I find that a bit unusual and thought about displaying it the other way around with negative numbers instead:

Read more

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

by

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. 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 … Read more

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

by

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:

Read more

Export large amount of data from Power BI desktop visuals

by

I’m going to show how to export data from visuals in Power BI Desktop that’s too big to be downloaded by the native functionality and therefore returns this error-message:

Export data from visuals

Check if you really need this

Although the method is fairly simple, there are simpler methods if you just need the raw data from your data model (and not the specific aggregations or measures that the visual contains):

Read more