SSAS MDX query
#MDXMonday, MDX, SSAS

#MDXMonday: Finding the Current Day

This week I’m teaching the Pragmatic Works Intro to MDX virtual training class. A student in the class asked how they could find the current day sales amount using MDX (no SSAS functionality) and I thought this was a worthy blog topic. This solution assumes that the cube is processed at least once a day as the query you’re about to see returns the last day in the cube that we data for.

View previous posts in the #MDXMonday series

The first part is where the most work takes place. I created a named set to identify

the last day in the cube that has a Reseller Sales Amount. 

WITH SET [Current Day] AS
   {
     Tail (
       NonEmpty ( [Date].[Calendar].[Date].Members, [Measures].[Reseller Sales Amount] ),
       1
     ).Item ( 0 )
   }

Reading the MDX from the inside out, I first use the NonEmpty function to identify only days in our date dimension that have a Reseller Sales Amount. So as long as we process the cube nightly, the last date in the set returned should be the latest day. Then I use the Tail function to return the last member in the set, which will be the last day.

Using this new set I created, called Current Day, I can then build calculations.

WITH SET [Current Day] AS
   {
     Tail (
       NonEmpty ( [Date].[Calendar].[Date].Members, [Measures].[Reseller Sales Amount] ),
       1
     ).item(0)
   }

MEMBER [Measures].[Current Day Sales Amount] AS

   Aggregate ( [Current Day], [Measures].[Reseller Sales Amount] )

MEMBER [Measures].[Current Day Order Qty] AS

   Aggregate ( [Current Day], [Measures].[Reseller Order Quantity] )

SELECT { [Measures].[Current Day Sales Amount], [Measures].[Current Day Order Qty] } ON 0

FROM [Adventure Works]

Here I created a couple calculated measures: Current Day Sales Amount and Current Day Order Qty. Here is the result from the query:

SSAS MDX find current day

I hope you found this useful!

Resources

MDXpert.com is a great site with references and examples for MDX functions and expressions.

Learn more about the MDX Nonempty() function here.