Calculate the difference between two dates in DAX

Another quick Data Analysis eXpressions (DAX) update, given that I’ve been playing about with PowerPivot again this week.  Following on from my exertions trying to work out how to return a month name in DAX, I found myself looking for a DAX equivalent of SQL Server’s DATEDIFF function.  Fortunately, this is reasonably straightforward.  All we need to do is perform a regular subtraction on two dates and multiply the result by 1.0, which will return the number in serial date-time (the number of days since “1900-Jan-0”, which is how Excel stores dates). Continue reading “Calculate the difference between two dates in DAX” »

Calculating Month Name from a Date Integer in DAX

I’ve been playing about with PowerView and PowerPivot recently, while also getting used to the Analysis Services (SSAS) Tabular model in  SQL Server 2012 (Denali).  The tabular model provides a high-compression, in-memory store for easy data model construction and analysis.  Ad-hoc calculated fields can be defined within PowerPivot (built on the same xVelocity technology) using Microsoft’s Data Analysis eXpressions language, which is very similar to Excel functions.  However, DAX is still pretty basic in some places, and while looking for a simple Month Name function (similar to the GetMonthName function in .NET) I ran into a couple of interesting issues. Continue reading “Calculating Month Name from a Date Integer in DAX” »

The common interface for SSAS query designer

How To Connect SSRS Report Builder to an SSAS Cube

I was asked recently how one could use SQL Server Reporting Services (SSRS) Report Builder construct a report using a SQL Server Analysis Services (SSAS) cube as it’s data source.  The good news is that is just as simple as connecting to a SQL Server database, there’s really very little difference, at least if you using the GUI route. Continue reading “How To Connect SSRS Report Builder to an SSAS Cube” »