Create list of accounts from NAV account schedules or COA totaling syntax

In this blogpost I’ll show you how to create a list of account numbers from the totalling syntax that you find in Dynamics NAV account schedules or chart of accounts for example:

1000..1090|1500|2000..2020

This string shall be transformed into a “real” list of account numbers in the query editor that can be used to select all accounts within those ranges.

  1. Split by pipe (“|”)

    • Text.Split(“1000..1090|1500|2000..2020”, “|”) splits the text into a list like so:

      NAV account schedules: 1) Split by pipe

  2. Split by dots (“..”)

    • In order to split each element of the list above by the 2 dots, I have to apply the split command to every item of the list. Therefore I use List.Transform like so:
      • List.Transform(SplitByPipe, each Text.Split(_, “..”)
    • This returns a nested list like below, with 2 list elements for those who contained a dotted range and just one element for the single ones like so:

      NAV account schedules: 2) Nested split by dots using List.Transform

  3. Create lists

    • In this step, the list of actual account number will be created. Again, I use List.Transform to transform the existing list by applying a function to each of its elements. The formulas required depend on the number of items in each list. If there is just one item in it (like the “1500” in the second row), nothing has to be done and the value can stay as it is (see yellow part in the pic below). But if the element contains 2 elements (like the “1000” and “1090” above), I create a list that starts from the first list element to the last. And as the elements in my list so far are in text-format, I have to convert them to number before when using them in the list creator ( {‚Ķ} in green ):

      NAV account schedules: 3) Create list of accounts

  4. Flatten nested list of lists

  • Now that I have all numbers per list element, I just have to combine the 3 list into one. The function List.Combine does this like so:

    NAV account schedules: 4) Flatten nested list of list

Further steps

From here, you can convert that list into a table and merge it with your chart of accounts as an inner merge. That will eliminate all the numbers that have been created above who might actually not have a match in the accounts table.

Function

For convenience, I’ve converted these steps to a function that take the original string as the only mandatory parameter. There are 2 additional optional parameters that take different characters for the pipe and dots if you want to apply this logic to a syntax that used different signs:

Enjoy & stay queryious ūüėČ

Stretching and Compressing Time Series with Power Query and Power BI

The good folks from SumProduct have published a challenge for Excel that I find worth doing in Power Query instead:

The Challenge

Stretching or compressing a forecast so that the proportion of the original series will be maintained:

The Function

This is not so easy mathematically (as the solution-blogpost explains in detail), so the code for the function isn’t short as well:

Read more

Right Aligning Text in Power BI: Format Improvements for Easy Profit&Loss Reports

Edit 14-Dec-2017: Now that we can right-align text measures in PowerBI, a SWITCH-measure like here: http://www.thebiccountant.com/2017/04/24/kpis-in-easy-profit-and-loss-for-powerbi/#comment-719 is the best alternative in my eyes. No need to read on ūüôā

As shown in my last part of the Easy P&L-series, Power BI unfortunately still lacks some fundamental formatting options like:

  1. Right aligning text (please vote for it here: Right align text in Power BI¬†– edit 15th Nov: We’re there: Right aligning text is available now: https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-november-2017-feature-summary/)
  2. Display numbers in different formats within one column (either to be implemented as a “neutral” format for Switch-measures, where the referenced measures carry the formatting attributes already, or as a part of a formula-based conditional formatting) (Thanks Matt for the voting-link: Conditional format SWITCH measure)

So for the moment I choose between the following workaround-options:

  1. Display %-values in a separate column
  2. Format numbers as text and fill up with spaces so that all end up right aligned
  3. See the suggestion from Matt Allington in the comments below (very nice)

Right aligning text or percentage figure in new column

For both options the preparations are the same:

Read more

Non-linear Break-Even Analysis in PowerBI

A break-even analysis tells you at which value of the parameter in question your profit-calculation will turn positive (link). Here we need to sell at least 173 at a given price of 20 before we’ve recovered all our costs:

If your variable costs are constant, you can solve it by this formula:

BreakEvenQuantity = Total Fixed Costs / (Unit Sale Price - Unit Variable Costs)

You’ll find tons of examples on how to do this in Excel like here .

Non-linear cost structure

But in real life, the variable costs often depend on certain quantities as you get discounts for purchasing large amounts. The following table shows a cost structure with fix costs in row 1. The 3rd column “FixOrQty” indicates if the cost item is fix or dependent on the quantity (Qty).¬†The 2nd row contains a variable cost that is constant with 2¬†for all quantities. Row 3&4 show a variable cost of 8 for quantities up to¬†100 and if you purchase more than 100 the costs will be lowered to 2 for all additional¬†quantities. Row 5-7 have a similar structure, but with 3 quantity ranges:

 Solve with goal-seek algorithm

Read more

KPIs in Easy Profit and Loss for PowerBI

Welcome to the last part of my Easy Profit & Loss series where I will cover KPIs in rows & columns:

1) KPIs in columns

Show all your figures as a percent of turnover for example: Nice & easy: Divide current figure by the total sum of turnover:

Turnover% =
ABS (
    DIVIDE (
[ActSign],
        CALCULATE (
[ActSign],
            FILTER (
                ALL ( IndividualAccountsLayout ),
IndividualAccountsLayout[Description¬†in¬†Report]¬†=¬†“Income”
            )
        )
    )
)
DAX Formatter by SQLBI

We need to leave the current row context to retrieve the turnover-value in each row, therefore the ALL.

2) KPIs in rows

Read more

Guide for switching Signs in Power BI and Power Pivot (bypassing Unary Operators in DAX)

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:

Table “Accounts”

1_SwitchAll

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.

2_SwitchExpensesLiabilities

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).

3_BWT (“BossWantsThat”)

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).

Reporting techniques covered with this approach

Read more