This page has 0 threads | Add post

Blogs

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

Ways to model different DAX calendar date situations Part two 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. Modelling techniques when working with calendars/dates Dealing with varying date granularity using calendars (this blog) Modelling bank holidays and other special days Modelling different financial year-ends 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.

Ways to model different DAX calendar date situations

Part two 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.

Modelling techniques when working with calendars/dates
Dealing with varying date granularity using calendars (this blog)
Modelling bank holidays and other special days
Modelling different financial year-ends
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

Dealing with varying date granularity using calendars

In the real world, not all data is tidily organised. This page shows how to cope with this!

Creating the worked example table of forecast data

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.

The aim: compare quarterly forecast data with total sales by quarter

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

Generating a date for each forecast row

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,

)

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.

Generating the measures needed

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

Creating the relationships

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.

Generating the pivot table

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.

Parts of this blog
Modelling techniques when working with calendars/dates Dealing with varying date granularity using calendars (this blog) Modelling bank holidays and other special days Modelling different financial year-ends Handling multiple date columns using multiple tables Handling multiple date fields using multiple relationships

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