BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
In the real world (or at least, the DAX version of it), calendars and dates aren't quite as simple as one would like. This blog shows how to solve a number of common scenarios, including where different tables have different levels of "granularity", coping with special days like bank holidays, handling non-standard finanical year-end dates and dealing with a table which contains more than one date column.
- Modelling techniques when working with calendars/dates
- Dealing with varying date granularity using calendars
- Modelling bank holidays and other special days
- Modelling different financial year-ends (this blog)
- Handling multiple date columns using multiple tables
- Handling multiple date fields using multiple relationships
This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.
Posted by Andy Brown on 22 January 2016
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Modelling different financial year-ends
Most organisations have a year-end which isn't the 31st December. This blog contains some ideas on how to model this.
For this blog I'm going to assume that your year-end is 31st March, but the techniques shown can be adjusted - in what I hope is a fairly obvious way - to suit any year end date.
Categorising dates by financial year
One way to work out in which financial year a date lies is to create calculated columns as shown below:
Here I'm using an interim column called FinYearInterim to make my formulae simpler and easier to read.
Here are formulae that you could use for each of the columns above:
| Column | Formula |
|---|---|
| FinYearInterim | =if(month([date])<=3, year([date]), year([date])+1) |
| FinancialYear | =([FinYearInterim]-1) & "/" & RIGHT([FinYearInterim],2) |
For the first column, this works out whether any given date is before or after March, when the financial year ends; the second column concatenates the previous year's number with the last two digits of this year.
This is a formula I put together myself; I suspect there's a more elegant way to solve this!
Using financial years in pivot tables
You could now use your FinancialYear column for reporting:
Here I'm showing total sales by species and financial year/month.
The results could look something like this:
The problem is that the months aren't correctly sorted - April to December should appear before January to March.
Sorting months correctly in the financial year
The solution to this is to create a new column sorting months correctly:
If the month number is less than or equal to 3 (ie if it's January, February or March), add 100 to the number.
You could now press F4 to show the properties of the MonthName column:
We want to change how the month name is sorted.
Choose to sort this column using our new MonthSortOrder column:
Specify which column SSAS Tabular should sort months by.
The result is that months and years appear where they should:
The months now come in the right order!
Getting cumulative totals and other functions to work
Many date functions have an optional third argument for the financial year-end to use:
A typical date function - the [YearEndDate] can be set optionally.
If you wanted to show the cumulative year-to-date total for some statistic, you could use a formula like this:
Year to date:=TOTALYTD(
sum('Transaction'[Quantity]),
Calendar[Date],
"31 March")
Note that SSAS Tabular is intelligent enough to realise that the 3rd argument should be taken to be the year end date, not a filter.
You could then display this in a pivot table to make everything hunky dory:
The year-to-date figures accumulate up to 31st March, rather than 31st December.
For the last two parts of this blog, I'll look at two different ways to solve the same problem: that which arises when a table contains two or more dates.
- Modelling techniques when working with calendars/dates
- Dealing with varying date granularity using calendars
- Modelling bank holidays and other special days
- Modelling different financial year-ends (this blog)
- Handling multiple date columns using multiple tables
- Handling multiple date fields using multiple relationships