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
This blog introduces some of the main useful date functions in DAX:
The main date functions which you can use in SSAS Tabular.
The functions that we'll meet are:
Blog | Functions |
---|---|
Cumulative totals |
TotalYTD, TotalQTD, TotalMTD DatesYTD, DatesQTD, DatesMTD |
Comparison with previous periods | SamePeriodLastYear, DateAdd |
Parallel periods | ParallelPeriod |
Moving averages | DatesBetween, NextDay, LastDate |
Semi-additive measures | FirstDate, LastDate, FirstNonBlank, LastNonBlank |
We'll begin with one of the simplest things you can do with date functions: show cumulative totals to date.
This page gives two different ways to calculate yearly, quarterly or monthly totals to date:
For example, the cumulative sales for March (2,259; shown shaded in green) should and does equal the sales to date for the year (that is, 733 + 699 + 827).
One way to solve this problem is to use the DatesYTD function:
The function returns the set of dates up to and including the "current" date in the specified year (the DATESQTD and DATESMTD functions return the equivalent dates for the current quarter or month).
The second argument allows you to change your year-end from the default of
December, as described in this earlier blog.
Here's a formula you could use:
Year to date:=CALCULATE(
SUM([Quantity]),
DATESYTD(Calendar[Date])
)
Here's the formula as a measure:
Calculate the total quantity sold, but instead of using the default dates for the query context, use the dates for the year up to and including the last date of the current period for each query context instead.
Calculating cumulative totals to date is such a common thing to do in DAX that there are dedicated functions to accomplish this task. Here's the syntax of one of them, the TOTALYTD function:
Sum an expression using a given calendar date field.
As for the DatesYTD function, you can change the financial year end as described here.
So our formula for the example above could be this instead:
YTD:=TOTALYTD(
SUM([Quantity]),
Calendar[Date]
)
Here are the two alternative approaches shown side by side, just to show that they give the same result!
The two functions give the same answers, reassuringly.
Time now to have a look at functions which give a comparison with the same period in the previous day, month, quarter or year.
It's often useful to compare a figure with the equivalent figure in a previous year. This page shows two ways to do this, using either the SAMEPERIODLASTYEAR function or the DATEADD function.
For example, the figure for January 2014 shows up as 733, since it's picking up the figure for the same time period 12 months earlier.
If ever a function was well-named, it's this one! Here's the syntax:
The function gives the set of dates for the same period, but 12 months earlier.
So a measure which would achieve the results shown above could be:
Last year:=CALCULATE(
SUM('Transaction'[Quantity]),
SAMEPERIODLASTYEAR(Calendar[Date])
)
And there's not much more to say about this!
I must admit I prefer this solution, since it's more generally applicable, the function name is shorter and I think it's easier to understand. The format of the DATEADD function is as follows:
Add a specific number of intervals of the required type to the current set of dates.
The arguments are:
Argument | What it means |
---|---|
Dates | The calendar dates corresponding to the pivot table query context. |
NumberOfIntervals | How many periods forward (if positive) or back (if negative) you want to go in time. |
Interval | Which period you want to use (MONTH, QUARTER or YEAR). |
Here's what our measure could look like:
Last year using DATEADD:=CALCULATE(
sum('Transaction'[Quantity]),
DATEADD(
Calendar[Date],
-1,
YEAR
)
)
It takes quite a bit of confidence to type the YEAR interval, since SSAS keeps trying to autocorrect it to the YEAR() function.
This measure should give the same result as the previous one:
The two measures compared (the results are the same).
Time now to look at a function which shows the value of a measure for the whole of a previous period: PARALLELPERIOD.
The PARALLELPERIOD function returns a set of dates shifted forwards or backwards in time. The easiest way to understand this sentence is by example!
The shaded figures show cumulative sales to date for 2014 (1,490) as a percentage of total sales for the whole of 2013 (9,470).
Here's the formula for the PercentLastYear measure:
PercentLastYear2:=TOTALYTD(
sum('Transaction'[Quantity]),
Calendar[Date]
) /
CALCULATE(
sum('Transaction'[Quantity]),
PARALLELPERIOD(
Calendar[Date],
-1,
YEAR)
)
That is, divide total year-to-date quantity sold by the total quantity sold one year ago.
The measure above shows a divide-by-zero error for 2013 figures, because the previous year's data doesn't exist. One way to get round this would be to use an IFERROR function to trap the error, and return blank:
PercentLastYear:=IFERROR(
TOTALYTD(
sum('Transaction'[Quantity]),
Calendar[Date]
) /
CALCULATE(
sum('Transaction'[Quantity]),
PARALLELPERIOD(
Calendar[Date],
-1,
YEAR
)
),
BLANK()
)
That is, if dividing the year-to-date total sales by the previous year's total sales returns an error, show a blank instead. This would give:
Much better!
Having gone through 3 relatively straightforward tasks, let's now look at something a bit more challenging: displaying moving averages.
There is no moving average function in DAX, so this isn't going to be straightforward! Here's what we'll produce:
For February 2014, for example (shown shaded), the monthly moving average is 794 (that is, 9,528, the quantity sold for March 2013 through to February 2014, divided by 12).
The moving average formula uses the NEXTDAY function:
Given a set of dates, this function returns the date immediately following them.
Our moving average formula also uses the LASTDATE function:
Given a set of dates, this function returns the last date amongst them.
Our formula could look like this:
Monthly moving average:=CALCULATE(
SUM('Transaction'[Quantity]),
DATESBETWEEN(
Calendar[Date],
NEXTDAY(
SAMEPERIODLASTYEAR(
LASTDATE(Calendar[Date])
)
),
LASTDATE(Calendar[Date])
)
)/12
To illustrate what this is doing, let's see what it does for the shaded cell (February 2014), breaking the function down into parts:
Part | What it returns |
---|---|
LASTDATE(Calendar[Date]) | For the corresponding dates (those in February 2014) this returns the last date among them (ie 28th February 2014). |
NEXTDAY( SAMEPERIODLASTYEAR (LASTDATE (Calendar[Date]) )) | For the dates for the same period in the previous year (ie the last day of February in 2013), find the following day (ie 1st March 2013). |
DATESBETWEEN( Calendar[Date], NEXTDAY( SAMEPERIODLASTYEAR ( LASTDATE (Calendar[Date]) )) , LASTDATE( Calendar[Date] )) | For February 2014, for example this will return the set of dates between 1st March 2013 and 28th February 2014. |
Simples!
The above formula contains a fudge: we've assumed that we'll always divide by 12 (for months), but this will give the wrong figures for early months (when we don't yet have 12 months of data to go on). To get round this, you could create the following formula for the number of periods:
Number of periods:=CALCULATE (
CALCULATE (
COUNTROWS (
VALUES ( Calendar[MonthName] )
)
),
DATESBETWEEN (
Calendar[Date],
NEXTDAY ( SAMEPERIODLASTYEAR (LASTDATE ( Calendar[Date] ) ) ),
LASTDATE ( Calendar[Date] )
)
)
Here's how this works. The second part of the formula returns (for February 2014) the period between 1st March 2013 and 28th February 2014, as we've seen:
This returns a period of up to 12 months.
The first bit of the formula then counts how many rows there are in the set of months contained within this range, to get:
The formula cleverly gives the right number of months even at the start of the first year.
Credit where credit's due: I adapted the idea from the divisor from this excellent article on moving averages.
Finally, you can divide total sales by this divisor to get the final moving average:
Monthly moving average:=CALCULATE(
SUM('Transaction'[Quantity]),
DATESBETWEEN(
Calendar[Date],
NEXTDAY(
SAMEPERIODLASTYEAR(
LASTDATE(Calendar[Date])
)
),
LASTDATE(Calendar[Date])
)
) / [Number of periods]
This gives the same figures, but will do so correctly even at the start of the first year.
For the final part of this blog we'll look at the grandiose-sounding semi-additive measures.
This page shows how you can use DAX measures to extract opening and closing balances from tables of data. You can try out the examples on this page by importing this file into your model, and linking the table created to the Species table and Calendar table:
The species balance table contains balances at particular dates.
Note that for the purposes of this blog, it doesn't matter what the balances are!
Nearly all DAX measures aggregate data:
What you get if you sum the balances from the species balance table.
Here's how the figure 66 for amphibians for January 2013 was reached, for example:
26 + 40 = 66, but sadly your closing monthly bank balance isn't calculated in the same way.
The FIRSTDATE and LASTDATE functions return the first and last days in any table of dates. You could therefore use the LASTDATE function to give (for example) the closing balance for any given period:
Halfway closing balance:=CALCULATE(
SUM(SpeciesBalance[Balance]),
LASTDATE(Calendar[Date])
)
Except that it doesn't often work!
The answers are a bit sparse! The problem is that for most of the balances there isn't a figure for the last day of the calendar month.
For example, for amphibians the only month for which we get a figure is July 2013, because there just happens to be a balance for this month for the final date in the month:
There is a balance for 31st July 2013 for amphibians.
These two functions give - more usefully - the last non-blank value of a function for a range of dates:
The syntax of the LASTNONBLANK function, for example.
Here's how you could use this expression to get the closing balance for each month for each species:
Closing balance:=CALCULATE(
SUM(SpeciesBalance[Balance]),
LASTNONBLANK(
Calendar[Date],
COUNTROWS(RELATEDTABLE('SpeciesBalance'))
)
)
That is, get the balance from the last date in the calendar table for which there are corresponding rows in the SpeciesBalance table.
If you're working a lot with opening and closing balances, investigate the ClosingBalanceYear function, and its equivalents for months and quarters (and for opening balances).
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.