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:
We also need a second table (ReportsAccountsLayout) that holds the definitions of the report-layouts like this:
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:
Duration per day on a Date-Time-level
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)
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.
Alberto Ferrari has recently published a very smart concept how to analyze events with a duration in DAX, which you should read here, if you haven’t done yet. It simplifies the necessary DAX-syntax and speeds up the calculations as well. My following approach simplifies the DAX-syntax even more, but it comes with a (very tiny) premium for performance and will also increase the file size a bit. So you have the choice 🙂
I’m transforming the calculated table into a “real” fact-table which enables me to use simple 1:n-relations to the other (now) dimension-tables:
The formula starts from Alberto’s first version, but uses the Date instead of the DateKey (yellow). Then there will be some columns added which we need for following calculations (green). Then you see that the DailyProductionValue is calculated at a different place and also has a much simpler syntax. At last there are some other columns for further calculations: “Shipped” and “Ordered” will create the bridge for the “missing” connections to the date-table:
Marco Russo has created a great tool for SSAS tabular that lets you edit measure definitions (which you should read here first if you haven’t done yet).
In this article I’ll show you how you can use it to import multiple measures from different tabular models into your current model.
The way the DAX-editor works is that it exports the existing measures from your model as a text file and imports them back after you’ve done your transformations. My technique will add the measures from the other model automatically to the existing measures so that both can be loaded back into your current model. In addition to that, you will have a UI with a process that guides you through the necessary steps that come with a task like that, which are:
select only those measures that you actually need
check references to existing measures and columns from the import model and manage their handling
allocate the tables into which these measures are going to be imported
This is about an easy way to create typical finance reports like Profit and Loss using DAX that (unlike all other solutions I’ve come across so far) can be handled with very basic knowledge of this language like this:
The trick that makes my solution so easy lies in the fact that it requires no aggregation functions of the output-mediums like:
pivot-tables: who struggle with asymetric logic and are not available in Power BI so far
cubefunctions: who are not available in PowerBI so far
So we have to build the details as well as all aggregations into the solution as it is and don’t rely on/use any aggregation functions (This means for Excel: We have to turn off subtotals as well as totals in our pivot tables. It means for Power BI: Hurray! Finally a solution where the lack of pivot-tables doesn’t matter).
The aim is to create a table/matrix with (account) details and aggregations into the rows and different slices of time-Intervalls or comparisons into the column sections. As for the columns, this will be covered by measures like [Actuals], [Budget], [PreviousPeriods], [Differences in all shapes…]. And – as the values in the columns should be the same – I’d prefer to use only one measure per column – that is fully sliceable and works on all (sub-) totals of course. … Ok – so some dreams later I found it:
So you just wrap simple measures like Act=SUM(Fact[Amount]), Plan=SUM(Plan[Amount]), DiffPlan_Act=[Plan]-[Act] … into the CALCULATE together with the bridge-table as the filter-argument:
This is the many2many-technique in it’s simplest form (PostFromMarcoRusso). It all goes via simple aggregation on all accounts found in the filter context:
Our bridge-table “AccountsAllocation” consists of one account number per simple account and has multiple rows for the (sub-)totals – being all accounts that belong to them:
The ConsKey stands for the row in our report (1) and the AccountKey_ holds the account numbers that are going to be aggregated (many (for the sub-totals) and 1 for the account-rows). So all we need is this unique and simple aggregation on AccountKey for every row in the report – with a filter from the Reports-table via our bridge table to the DimAccounts, who then filters our FactTables: 1 -> many -> 1 -> many.
His solution has features that would do my version good as well:
Directly connect to the model to be analysed without clumsy export of measures to txt via DAX Studio
Including calculated columns? No!: Who does calculated columns in DAX in PBI? Do them in the query editor using M instead (more functions, better compression, easier merge of model to SSAS once needed)
So wouldn’t it be cool if we could just add a documentation page to our current model – “all in one” so to speak? Here you find how to “hack”-connect with Excel to your current Power BI Desktop-Model.
So what works with Excel should work with PBI as well – just that we need to connect via the query-editor, using M. And of course: As we’re hacking ourselves here (i.e. the file we’re currently working on), we need to save our changes in order to make them being shown.
This DAX-VizArt-Wizard vizualizes dependencies between your DAX measures, shows the definition of all related measures and shows differences between the measures of 2 models/versions. This works for Power Pivot, Power BI and for Analysis Services Tabular (SSAS).
In the Power BI-Version you’ll see them in the Sankey-chart like this:
If select measures/nodes, all direct connections will be highlighted:
In the second version, all indirect connections will be highlighted as well & the selected measure definitions will be shown.
When reading horror-stories about Excel-hell describing how dangerous it is to use Excel in corporate environments, I cannot help but to think of this hilarious videodescribing the fatal consequences of acting without common sense: Just don’t do stupid things with it.
Although Excel comes nearly for free (in relation to what value it delivers) this doesn’t mean that you don’t need to invest in applying proper techniques (like in any other profession). There’s training and best practices for every different need.
But the best thing about Excel seems largely unknown still: Since the invention of Power Query it has never been easier to be save around Excel than before: A magic tool that can solve many of the problems that cause Excel hell: Repetitive tasks: The little adjustments and extensions that pile up when you use your workbook again and again and are often performed without realizing the (meanwhile complex) context of all the standard-Excel-elements involved: Power Query will prevent this mess. It will help you organize and automize your repetitive tasks in Excel.
Creating a Dimension table from a fact table using Power Query is really straightforward using the Remove Duplicates function.
However – you might experience a problem if the key to your Dimension table that you’re extracting from the Fact table is text and not number format. Power Query is case sensitive and will consider “Car” and “car” as different, returning both after the remove duplicates step. Once you load this into your Power Pivot data model, it will be shown there as “Car” and “Car” or “car” and “car”, depending on which term was the first in the list (will always take the first one).
This further means that you will not be able to connect you new Dimension table to your Fact table as the Dimension table now has dups.
To overcome this (and because it might be good practice anyway):