Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
581 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
Written by Andy Brown
In this tutorial
The previous blog in this series showed how to create and use a basic calendar. This blog shows how to cope with the following scenarios which can arise in "real life":
Scenario | Notes |
---|---|
Varying date granularity | How to cope when some data is at date level, some by month and some by quarter. |
Bank holidays | The best way to integrate special days like bank holidays into your calendar. |
Financial years | Coping with the common scenario that your financial year doesn't end at the same time as your calendar year. |
Multiple date tables (1) | Using multiple tables to cope with the situation where a single table contains two or more dates (for example, an order table might have an order, shipping and invoice date). |
Multiple date tables (2) | Using multiple relationships as a way to model the same thing, but this time including your calendar table only once. |
Even if your interest in calendars is waning it's still worth reading this blog, as many of the techniques introduced have much wider application within SSAS Tabular.
In the real world, not all data is tidily organised. This page shows how to cope with this!
You can try this out yourself by running this SQL Server script to generate this table of forecasts:
The table of forecasts generated by this script.
You can then import this into SSAS Tabular to get this table:
The same table imported into your tabular model.
This blog shows how to generate a pivot table comparing actual and forecast sales by species and quarter:
This page will show how to compare actual and forecast data (here we're just showing the Amphibian and Bird species).
The simplest way to link the imported forecast table to your calendar table is to create a date column for each quarterly sales forecast row:
This calculated column returns the first date for each quarter.
This formula takes each quarter, extracts the right-most character (the quarter number) and generates a month number from this which feeds into a date formula:
=date(
[Year],
right( [Quarter], 1) * 3 - 2,
1
)
The formula calculates the first date for each quarter. It doesn't matter which date you construct, as long as the quarter it belongs to is correct.
To compare total forecast data for a quarter with total actual sales for the quarter, we'll need two measures. Here's the measure for the forecast table:
The measure sums forecasts, but if we display data by quarter and species each cell will actually only "sum" a single value.
The measure for the transactions table to show total sales could look something like this:
This measure shows total sales (price multiplied by quantity).
In addition to the existing relationships in your model, you now need to create some additional ones:
For each forecast we can pick up on the species it applies to and for which calendar date it was made.
You can now generate the pivot table comparing actual and forecast sales:
You can summarise the forecast and actual sales by year and quarter.
Note that if you delved any further into the calendar (for example if you tried to summarise by month) you'd get strange results, because the forecast data was only available at quarter level. It's up to you not to ask for silly pivot tables!
Here's the pivot table (having filtered it to show only amphibians and birds):
The pivot table, comparing the quarterly forecast with the sum of actual sales for each quarter.
You may be worrying that attempting to show forecast data at monthly or daily level will give meaningless or even misleading data, but there's an easy way to avoid this happening; just take care not to expose the forecast figures and months/dates in the same model perspective.
This blog explains how to show for each calendar date whether it is:
You can apply similar principles to categorise dates in any other way that you choose.
There's no getting around it - to get this to work you're going to have to beg, borrow or type in a table of bank holidays for those years in which you could conceivably have an interest:
For this example, you can download the Excel workbook of bank holidays for 2013/2014 for the UK.
Once you've got your hands on a workbook, table or CSV file like this, you should import it into your model:
When importing the data (I did it via SQL Server), I've given it the friendly table name BankHoliday.
You should now have another table in your model:
The imported table of bank holidays.
You should now link the calendar and bank holiday table - but carefully:
You should drag the calendar Date column onto the bank holiday BH_Date one.
Normally it doesn't matter which way round you drag, because SSAS Tabular can work out the nature of the relationship from the data in each of the two tables. Here it can't, so you need to be careful. The way to think of it is that for each date you are looking it up in the bank holiday table to see if it exists.
To find out if a day is a bank holiday or not, you can use the IF and RELATED functions introduced earlier in this series to answer this question:
A formula that you could use is shown below:
This formula would determine whether a particular date is a bank holiday or not.
You could actually abbreviate this to:
=(RELATED(BankHoliday[BH_Date])<>BLANK())
This proposes the statement that there is a corresponding row in the bank holiday table for the current calendar date, which is either true (in which case it is a bank holiday) or false (in which case it isn't).
Finally, we need to see if a date is either a bank holiday or a Saturday/Sunday (we're using UK weekends here). To do this you could use the WEEKDAY function, which has the following return types:
Return type | Week definition |
---|---|
1 | Week runs from Sunday (1) through to Saturday (7) |
2 | Week runs from Monday (1) through to Sunday (7) |
3 | Week runs from Monday (0) through to Sunday (6) |
We'll use the return type of 2, to get the formula for the IsWorkingDay function shown below:
The formula to use, explained below. New Year's Day in 2013 was on a Tuesday.
The formula uses the || double-pipe symbol (look at the bottom left of your keyboard, next to the SHIFT key) to denote OR. Here's the expression in full:
=IF(
[IfBankHoliday] ||
WEEKDAY([Date],2) > 5,
false,
true
)
Thus if the data is a bank holiday or it's on a 6 (Saturday) or 7 (Sunday), it isn't a working day; otherwise it is.
The main reason to create a new way of categorising a date is so that you can create pivot tables like this:
Total sales were higher on working days!
For this particular pivot table, you'd have to tweak the calculated column to show text strings rather than just true/false:
=if(
[IfBankHoliday] ||
WEEKDAY([Date],2) > 5,
"Lazy day",
"Working day"
)
Next in this blog I'll show how to model the similar situation where your financial year doesn't end nicely on 31st December.
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.
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!
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.
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!
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.
A common situation in SSAS Tabular is that you only have one calendar table, but you want to use it on multiple occasions:
Suppose you want to summarise staff by when they were born and when they joined, using the two date fields shown selected. How can you do this?
The output we're trying to produce is this:
For example, there are 4 members of staff who were born in 1995 and who joined the MAM company in 2011.
I'll first show how to set up this model, then how to solve this problem.
If you want to follow along with this case study, you'll need to make sure that your calendar contains dates going back to the earliest year when someone might have been born. To do this, follow these instructions to create a calendar table, but change the start date to (say) 1st January 1950:
Change the stored procedure to start much earlier, recreate it and re-run it, as described here.
Now create a new data model using the MAM database and import these two tables:
Import the tblStaff and tblCalendar tables, and give them friendly names as shown here.
In the staff table, create a measure to count how many staff there are for each query context:
A measure to count the number of staff for any pivot table cell.
The problem comes now: which of the two date fields do you join to the calendar date column?
You can't join by both dates, otherwise you won't get any data in your pivot table.
The best solution, I think (I'll show another one using multiple relationships in the final part of this blog) is to import your calendar table multiple times. First rename the one you have:
Rename the calendar table you've imported (let's say it will link to the birth date, so we'll call it BirthCalendar).
Now create a relationship between the BirthDate column and the BirthCalendar table:
Drag the birth date column onto the calendar date column.
Now go back into your existing connections:
The menu option to view your model's connections (there's a tool to do the same thing on the SSAS toolbar).
Double-click on the connection you created to import your tables:
I had the foresight to rename mine as MAM.
Choose to import the calendar table again, but this time call it JoinCalendar:
Here we've used the friendly name JoinCalendar.
Drag the DateJoined field from the Staff table onto the Date field in the JoinCalendar table to create a second relationship:
Creating the relationship to find the year in which people joined.
You can then create the pivot table shown at the start of this blog:
It's not obvious that the Year on the left is the birth year and the Year at the top the joining year, so it might be an idea to go back and give your calendar columns less ambiguous names.
The final result:
The only tweak I've done is to change the sort order for the two years.
Purists may not like this approach, since it involves storing the rows in the calendar table multiple times (once for each date). However, it's easy to understand and use, and memory is rarely that much of a constraint.
For those who don't like the concept of importing multiple calendar tables, try the final part of this blog, which shows how to create multiple relationships instead.
The previous part of this blog showed a problem - how to join two columns simultaneously to the calendar's Date column:
The problem: we can't join both columns using the same relationship.
One answer - shown in the previous part of this blog - is to import a second copy of the calendar table. The other possible answer is to create two different relationships, and then specify within each measure which one we're using.
Please first follow the steps in the previous part of this blog to recreate the model containing the two tables shown above (including expanding the calendar table to go back to 1st January 1950).
Start by creating the first relationship:
Drag the DateBorn column (for example) onto the calendar Date column.
Now drag the DateJoined column onto the Date column to create a second relationship:
Create a second relationship, this time using the staff joining date, not the birth date.
SSAS shows the two relationships like this:
The one with the solid line is the active relationship.
The first relationship that you created is the active one (you can only have one active relationship between any given pair of tables), as double-clicking on it shows:
Double-clicking on the solid line shows that this is the active relationship.
The CALCULATETABLE function works in the same way as the CALCULATE function, except that it requires a table as the first argument rather than an expression:
The CALCULATE function returns a value, whereas the CALCULATETABLE one returns a table of values.
By default, when you refer to any expression which requires Analysis Services to look between tables, it will use the default relationship path to do this:
Where two or more relationships exist between a pair of tables, Analysis Services will use the one marked as active to connect them.
However, you can change this behaviour by using the USERELATIONSHIP function:
You can now create the following two measures:
Create the two measures shown in the staff table, using the formulae below.
Here's the measure to count the number of staff joined:
Number staff joined:=COUNTROWS(
CALCULATETABLE(
Staff,
USERELATIONSHIP(Staff[DateJoined], Calendar[Date])
)
)
And here's the measure to count the number of staff born:
Number of staff born:=COUNTROWS(
CALCULATETABLE(
Staff,
USERELATIONSHIP(Staff[DateBorn], Calendar[Date])
)
)
The only difference is the relationship they use to pick up on any calendar constraints from the calendar table.
Using this method, you can't create the pretty diagonal effect as shown in the previous part of this blog, but you can compare the numbers born and joined for any year:
Again, the only tweak I've made is to sort the years into reverse order.
The pivot table fields are (I hope) fairly obvious:
I've just asked to show the two measures created.
Personally, I much prefer the previous approach! I'd rather have multiple tables (each having a separate relationships) than have multiple relationships to the same single table.
You can learn more about this topic on the following Wise Owl courses:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.