In finance & accounting, you very rarely report the figures with the signs of their source systems, but switch (certain) signs according to different needs. Instead of using unary operators for it, I’ll present an easy and dynamic way for it in Power BI and Power Pivot using DAX. It will cover the following 3 main scenarios:
1_SwitchAll: All signs are switched (red)
2_SwitchExpLiab: Expenses and liabilities are switched back to their original values (green)
3_BWT_Indiv: Only the main figure for expenses (or liabilities) carries a minus, all following positions specifying the expenses are (principally) reported as positives (blue)
Switching signs in Power BI and Power Pivot without unary operators
I’m using the sample data from this article but changed the source-data to a double-bookkeeping structure. There signs are used and the transaction entries in your ledger table always add up to zero. This is a method that prevents errors when posting and can also be used to prevent errors in reporting. If you keep the signs in your reporting system, all you have to do is add up the relevant figures and the returned (absolute) figures will always be correct. If you have read my previous articles on Easy P&L, you have seen this method in action: No minus-operation there, just a simple stupid adding of all accounts who fall into several (sub-) total categories via the bridge-table.
The Account-table also contains of (sub-) totals and the column “AccountType” shows if the positions are regarded as Turnover (Revenue) or Expenses:
My values on “1_SwitchAll” corresponds to “FinalValue” in the article above. The revenues come from consultancy and coursed provided. But the revenue for courses don’t just consist of attendee rates, but the costs for catering and paid instructors shall be deducted (highlighted in yellow). So the “good” numbers that contribute to cash in your pocket shall be reported without a sign and the “bad” numbers that result in an outflow of cash shall be reported with a minus. Within the expenses category, the costs carry a minus and the travel refunds (highlighted in orange), which are cash positive, are reported as positives.
Another requirement that is often used for balance-sheet-reporting or reports that only report on cost-situations, require that the costs or liabilities are reported without signs. … Principally, because the reimbursements/cost deductions shall be reported with an opposite sign (to show the adverse effect to the cashflow). This is what “2_SwitchExpLiab” shows (not covered in the article).
Last but not least comes a typical “BossWantsThat”-requirement: Basically some strange stuff that you just have to deliver. Here the main categories “Revenues” and “Expenses” shall be shown with the signs that reflect the cash-direction, but all specifications that follow below shall be reported without signs (again: Principally, because positions with opposite cash-effects than the main category shall carry inverted signs).
If you use DAX to flatten Parent-Child hierarchies you will end up with a table that has a static number of columns (like described here). If you need a dynamic solution instead, which creates just as many level-columns as there are needed for the current data, you can use DAX’s helper-tool Power Query (or Get Data in Excel) or the query-editor in PowerBI, which uses the language M.
Another advantage of this solution is that you can script the table creation in one step (only flaw: You still need to manually adjust your hierarchy though): But it saves time in creating the table, especially if you have many levels.
2 simple steps
copy the following function,
add a new step to your current table where you call this function, filling in the following parameters:
table name (which is the name of the previous step in your M-query)
name of the column with the child-key
name of the column with the parent-key
name of the column who’s values shall be shown in the levels (can also be child-key)
And this is the code, which you can also download below:
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.
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