Save content
Have you found this content useful? Use the button above to save it to your profile.
New year
iStock_Matejmo

Power BI: It is 2018 but what year is it?

by
9th Jan 2018
Save content
Have you found this content useful? Use the button above to save it to your profile.

If today you were to stop a student in the street and ask “What year are you in?” you could get many different, yet valid answers such as: 2018, 2nd or 20th

The first is obvious and would have been a different answer just a few days ago. The second could be that they are in their second year of a course in the college and has probably not changed in the last few days. The third could be that they will turn 20 on their next birthday and this may or may not have changed in the last few days.

The point is, that they are all different answers; each is potentially correct depending on the context of the question. They also don’t increment together. At least not necessarily.

It is the same with accounting data. If you were to look at your Sage 50 Accounts system there are at least three possibly valid answers to to the question “What is the current year?”:

  • The accounting year of the company  – which may or may not be the same as the calendar year.
  • The current “unclosed” year  – which may be the last accounting year or this current accounting year and may or may not be the current calendar year.
  • The current calendar year.

If you are an accountant in practice and working with Power BI to deliver reports for your clients, then having a consistent and automated way of handling this ambiguity will, well, eliminate the ambiguity and save you a stack of time. With this extra time you can well, sign up for this free course on Power BI from Microsoft that started this week :).

Generally, you are probably trying to produce charts like this, that start at the beginning of a client’s financial year, and not necessarily on 1st January:

Power BI and Dates

Power BI works really well with dates and I guess if it didn’t it would not be much good for analysing accounting data. It comes with a stack of Date & Time Functions and newer Time Intelligence Functions that you can use to analyse date-driven data. Those of you familiar with Excel will be familiar with many of these. Added to this, it automatically adds additional virtual columns to your date fields, splitting them out into Year, Quarter, Month and Day so that you can drill down through this hierarchy without writing a single line of DAX code.

In Power BI you can work really easily with standard calendar year dates within your low-level data model, or on the screen visualisations with the Date Slicer or through the in-built date hierarchy in Power BI.

This is nice. It would be even nicer though if you lived in a world where all of your clients operated using the calendar year as their accounting year and where they all closed off their years accounts at midnight on 31st December.

The problem is that if you are practicing in the UK this is not likely. So what do you do about it? Well, again I present you with three options:

  1. Lobby Microsoft for a special UK calendar in Power BI that runs from 1st April to 31st March for UK companies (or perhaps a more general, flexible solution).

  2. Relocate your practice to a country like Hungary where it is normal (I believe it used to be or maybe still is mandatory?) to have a fiscal year that this the calendar year.

  3. Get on with a workaround.

It is the latter option that I present here.

The three problems we need to solve for accounting year analysis in Power BI

Ok, so we are back to groups of three again. I will illustrate the problems with a backdrop of Sage 50 Accounts data, because that is my experience and because Sage 50 is so widely used in the UK. The general problems are common though.

  1. If you are working with multiple clients on any scale and want to avoid over-customising your reports for each client you will need an automated way to determine you clients’ year end.

  2. You will need to redefine your month numbers so “Month 1” is for example April and not January. From this you can also redefine your Quarters so for example Q1 becomes April through to June.

  3. If you want to do Actual versus Budget analysis from Sage 50 data, you will also need to handle the situation if the previous accounting year is not closed off  – what do we mean by “Month 1”? Is it Month 1 of the current financial year, or that of the previous year? In Sage 50, budgets are maintained only for the current open financial year. If we are past year end and the previous year accounts are still open, then in Sage 50 budgeting “Month 1” still means the first month of the last financial year.

In Sage 50 Accounts you will find an ODBC Periods table with accounting period information. The problem is that it is not in a form that you can readily use. It is in a text format and you will need to extract the dates you want from this text. If you are used to working in regular Excel (as opposed to Power Query) you may be tempted to do a bit of text processing, but Power BI Desktop offers you a much slicker way of doing that which I will show you here.

All the examples that I am presenting here are what I currently use in my own model, and if you want to try it out with your own Sage 50 data, the easiest way is to install the Suntico connector for Sage 50 and I can send you a copy of my Power BI model in template format that works out of the box with this connector. There you can examine, adjust and extend my model to your heart’s content. Alternatively you can just follow the steps presented here and use in your own model. If you are not using Sage 50 Accounts, then I still think that the general ideas are likely to apply but the details and certainly the code will be different.

The first thing I will explain is the end goal. Where do I want to get to? After that I will describe the details of getting there along with some sample code.

Where do I want to get to?

In simple terms what I want is for my visualisations to display the correct year, quarter and month based on the accounting year of the dataset. Since my Power BI reports are going to be consumed by “normal people”, I want to avoid labels my reports saying things like “PriorYearMth2”. And instead say “May 2016” (or “2 - May” so that it sorts correctly), and that this belongs to Q1 and not Q2, and that it is the second column in a 12-month bar chart instead of the fifth column.

To achieve my goal, the first thing that I need is a reference date that Power BI can understand, such as the [First day of this accounting Year]. From this, I can work everything else out.

The next design consideration is how much I want to reply on my Dates table using pre-calculated columns, vs putting the logic into each date-related measure. Since this logic is going to be used throughout my model as a basic building block; that I want to create date slicers based on the accounting year; that I want the model to be understood, maintained and built on by other people; that many users are going to be web users; and that performance is not a major consideration; I decided on a fairly comprehensive set of building blocks in the Dates table as Calculated Columns. On top of this I can build (and have built) a range of date-related measures.

So my end goal was a dates table that would look like this:
 

Apart from the first Column “Accounting Period”, none of this is specific to Sage 50 so far.

Now to get here I needed to look at my Sage 50 Accounts data and figure out how to get that one reference point such as the [Start of this accounting Year].

My starting point with Sage 50

My starting with Sage 50 data is an ODBC “Periods” table that gives me two columns: the Accounting Period and its Description.

You can see very quickly that I have the information I need; it is just not in the form I that I need it. I need to convert this text information into something more useful. Fortunately, it is very simple and straightforward.

Transforming the Periods table

What I want to end up with for my periods is a table that looks like this:

To do this I will use a combination of the Power BI Query Editor to extract the [Start of Period], [End of Period] and [Days in Period], [Full Month Name] and [Calendar Year] information from my Sage 50 source data. Then I will use a simple text function in DAX to create my [Month Text] column.

Step 1  – extracting the date information from the plan text Sage 50 data source using Power BI Query Editor

I could do this step in DAX using the various text functions, but it is quite arduous and there is a much simpler way to do it in the Power BI Query Editor. Additionally, as this is preprocessing the data as it comes into my model I have a suspicion that it will also be more efficient.

Rather than document all these steps one by one, I have prepared a short video for you that takes you through the whole process. The bottom line is that this is a really powerful and fast way to extract date information from text.

(You will see on the video that I accidentally deleted the [Full Month Name] and [Calendar Year] fields, but you can also see how not to do that :) )

The last column that needs to be created is the [Month Text] column which I will use then in my Dates table so that I can have my months labeled and sorted according to my accounting year, as well as create a month slicer based on my accounting year.

Step 2  – Create my calculated column [Month Text]

Calculating [Month Text] is now really simple. It is just:

[Month Text]

Month Text =
'Accounting Periods (Months)'[Accounting Period] &
" - " &
LEFT ( 'Accounting Periods (Months)'[Full Month Name], 3 )

Step 3  – Create some essential date Measures from this Accounting Periods table

Now that I have my Accounting Periods table created, it is time to create some useful Measures that I can reuse throughout my model for dates calculations. I chose to use Measures rather than Calculated Columns here because I am just trying to calculate single values rather than whole tables. I created five measures that define the:

  • Start of next year,

  • Start of this year,

  • Start of last year

  • Start of the year-before-last

  • End of this year

I will give you an example for [Start of this Accounting Year].

So the first day of this accounting year for the company in my dataset is 1st April, 2017. You would think that I could simply see what the [Start of Period] is for Period 1. It is not that simple though.

If the previous year (year ending 31st March 2017) has not yet been closed off in Sage 50, then this [Start of Period] date will show 1st April 2016 for Period 1. This is not the start of what I want to call “This Year”.

We need to determine if more than a year has past since the [Start of Period] for accounting period 1 and adjust accordingly. That is done with this Measure:

[Start of this Accounting Year]

Start of this Accounting Year =

Var

FirstDayOfPeriod1 =

CALCULATE (
MAX ( 'Accounting Periods (Months)'[Start of Period] ),
'Accounting Periods (Months)'[Accounting Period] = 1
)

Var

AccountingYearTest =
DATE ( YEAR ( TODAY() ) , MONTH ( FirstDayOfPeriod1 ), DAY ( FirstDayOfPeriod1 ) )

Return

IF (
AccountingYearTest > TODAY(), DATE ( YEAR ( TODAY() ) -1,
MONTH ( FirstDayOfPeriod1 ), DAY ( FirstDayOfPeriod1 ) ),

  AccountingYearTest

)

In this case I am defining variables in my Measure to make the calculation easier to manage and follow.It is easiest to understand if you first look at the last IF statement after “Return”..Basically I am saying that if you take today’s year and the month/day of the [Start of Period] value for Period 1, then subtract a year, is this less than the [Start of Year] date.

Now I have my Measure [Start of this Accounting Year] that we can reliably use throughout the model and to calculate the other Measures that define the starts of the different accounting years.

Step 4  – Define the year-based analysis periods in the dates table

This last step is the easy one and I won’t go through every example. But to create the Calculated Column [This Year] in the ‘Dates’ table is very simple:

[This Year]

This Year =

IF ( Dates[Date] < [Start of this Accounting Year], FALSE(),

IF ( Dates[Date] > [End of this Accounting Year], FALSE(),

TRUE()

  )

)                           

Summary

Getting your dates in Power BI to line up with your accounting years is not simple but it is straightforward. It just requires a bit of thought.

  • Think about your end goal. If you want to have date slicers and months displayed in sequential order based on your accounting year then I think you have to put these building blocks into your Dates table.

  • Pay attention to what your accounting data tells you in the event that you have not yet closed off the previous accounting year. I think that for most of the time in Power BI you will want to display “This Year” as the new current year, regardless of whether you previous year has been closed off.The example I have shown here works with Sage 50 Accounts data. The same principles, if not the detail, will also apply to other accounting data.

  • You are welcome to cut and paste these code samples for your own use, or to try out my Power BI Desktop template file directly with the Suntico BI connector for Sage 50.

LIke all things with Power BI there are many ways to achieve the same goal. Some easy and some not so easy. I think that this is a pretty good and easy way to extract the accounting year information you need from a text field that you may have in your accounting software. I am sure that some of you may come up with better, slicker suggestions and I would love to hear them. We are all here to learn.

Oh and on the subject of learning, nothing beats a bit of structured training. So why not start the New Year by learning a new skill on this Free Microsoft self-paced and online course for Power BI that started this week?

Useful links

Tags:

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.