This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

How to use DAX date-based functions in Analysis Services tabular model Part six of a six-part series of blogs
There are some wonderful date functions in DAX, with evocative names like TotalYtd, SamePeriodLastYear and ParallelPeriod. This blog shows how to use DAX date functions to summarise data, assuming that you have already created a calendar table. Creating date-based functions in DAX in SSAS Tabular Cumulative totals (TotalYTD, DatesYTD, etc) The same period in a previous month, quarter or year Using PARALLELPERIOD to get the whole of the last period Moving averages using DAX date functions Semi-additive measures in DAX (this blog) This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

How to use DAX date-based functions in Analysis Services tabular model

Part six of a six-part series of blogs

There are some wonderful date functions in DAX, with evocative names like TotalYtd, SamePeriodLastYear and ParallelPeriod. This blog shows how to use DAX date functions to summarise data, assuming that you have already created a calendar table.

Creating date-based functions in DAX in SSAS Tabular
Cumulative totals (TotalYTD, DatesYTD, etc)
The same period in a previous month, quarter or year
Using PARALLELPERIOD to get the whole of the last period
Moving averages using DAX date functions
Semi-additive measures in DAX (this blog)

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 08 February 2016

Semi-additive measures in DAX

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!

The problem with opening and closing balances

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.

A partial solution - the FIRSTDATE/LASTDATE functions

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.

A fuller answer using FIRSTNONBLANK and LASTNONBLANK

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).

Parts of this blog
Creating date-based functions in DAX in SSAS Tabular Cumulative totals (TotalYTD, DatesYTD, etc) The same period in a previous month, quarter or year Using PARALLELPERIOD to get the whole of the last period Moving averages using DAX date functions Semi-additive measures in DAX (this blog)

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR