564 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls 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 ...
Ways to model different DAX calendar date situations Part four of a six-part series of blogs |
---|
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.
This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources. |
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.
Parts of this blog |
---|
|
Some other pages relevant to the above blogs include:
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 2023. All Rights Reserved.