Power BI

Conditions in FirstN, LastN and other xN-functions in M, PowerBI and Power Query

by

Today I discovered that we can use conditions in many of the N-selecting functions where one/I would normally expect just a number-expression for the N: Table.RemoveFirstN( table as table, optional countOrCondition as any) So apart from being able to select a certain number of rows to be removed, we can pass a condition (as function). This condition will … Read more

Comfort Functions for Easy Profit & Loss statements in Power BI and Excel – Part2

by

Here comes some long awaited comfort functions for part 2 of my easy P&L series. In the first section I’ve presented the general principle on how to work with a structure using an accounts-group-table. Today I will present 2 alternatives to define the reports without specifying single accounts. So if a new accounts are added to the chart of accounts, you don’t have to adjust your report definitions: Just make sure that to fill in all the fields in your account-group-table and you’re ready to go 🙂

No need to specify single accounts

So you only need to adjust your report definitions if you add new group items. If that’s still too much, take the 2nd solution, which will even eliminate that requirement:

  1. Individual Account Layout: Just define each subtotal and determine for which subtotals single accounts shall be shown
No more specification of individual accounts

How to use it:

How to use Individual Report Layout

2. Ultrashort Account Layout: Further simplification of just defining the groups (hierarchy) that shall be shown (with option to filter on one of them)

No need to define individual group items

How to use it:

How to use Ultrashort Layout

So these 2 different layouts will both produce the same reports incl. all accounts – just like in the first example. So you can choose which layout-style suits you best – actually, you can use all 3 in parallel. You just have to make sure to grab your pivot-rows from the correct tables and in Excel to grab the matching measures, as they all have their own bridge-tables (which need to be used in the measures):

 

How it works

Read more

Easy Profit & Loss (and other account) statements in PowerBI and Excel – Part2

by

Welcome to part 2 of my series of easy Profit & Loss and other account statements in Power BI and Excel. In the first part I introduced the general principle of creating asymmetric shaped reports who use just one measure per column (you should have read this article in order to understand this post here).

How the technique works

This technique capitalises the aggregation power of the Vertipaq engine and creates a bridge-table between your DimAccount-table and the ReportsAccountsLayout-table. In there for every line of your report, all accounts that belong to the (sub-)totals are matched (“AccountsAllocation”). This table can get very long, but the engine can handle this easily:

Different use case: Account-groups-tables

In the first example we’ve worked with a chart of accounts, which had a parent-chield-hierarchy defining all the subtotals of the report. In this example we’re working with a different setup, using the good old DimAccountsGroups-table. Just one row per account and the columns are coming in pairs, containing the group-criteria and the sort-order for the report:

Individual Report-Layout

We also need a second table (ReportsAccountsLayout) that holds the definitions of the report-layouts like this:

Read more

Incremental Load in Power BI using DAX UNION

by

This is an old post. Meanwhile we can implement proper incremental refresh with Power BI licenses: https://docs.microsoft.com/en-us/power-bi/admin/service-premium-incremental-refresh Warning – edit 03-Jan-18: This method doesn’t work in the service unfortunately. Please upvote the bugfix here: https://community.powerbi.com/t5/Issues/quot-Include-in-report-refresh-quot-disabling-doesn-t-work-in/idi-p/331982#M19945 This article describes the latest workaround for incremental load in PBI (thx to Taylor Clark for stressing this out!). It’s not very dynamic, … Read more

Dynamic & bulk type transformation in Power Query, Power BI and M

by

This is just a quick code-share-of-the-day of different scenarios for dynamic type transformation of multiple columns at once. The syntax to transform the format of 2 columns (“Column1” and “Column2”) in a table (“Source”) looks like this: Now if you would like to do one of the following: 1. Force all columns of the table to … Read more

Dynamic duration calculation using DAX in Power BI and Power Pivot

by

While it is fairly easy to calculate the difference between 2 dates in DAX using DATEDIFF, it is a bit more demanding if you want to exclude weekends and holidays or filter the duration on certain date-intervals, so only get a part of it. Also if you want to return on date-time-level instead of only counting net-workdays.This is where this new technique for dynamic duration calculation can come in handy.

We can use the basic technique that I’ve described here and modify it by adding 2 columns to the calculated table:

  1. Duration per day on a Date-Time-level
  2. Marker-column if weekday or not (this assumes that you have a column in your date-table which indicates if the day shall be considered as weekday or not)

1_duration_calculation

The duration-calculation needs to handle the cases where only parts of the day are to be counted: If the event starts and ends at the same day, the difference between those figures has to be taken. If on the other hand, the event spans multiple days, for the start-day the time until the end of the day has to be calculated while for the end-days the time from the beginning of the day is the right one. The other days count as full days with 1. Hence these 4 cases.

Let’s have a final look at our simple measures:

Read more