562 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
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.
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:
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.
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.
|Parts of this blog|
25 Aytoun Street