ssas mdx query for previous previous month

Question

am currently building a ssas cube. I need the default to bring back 2 periods in time. Last month and the month prior to that.

For last month I am using

tail(nonempty([service date].[quarter no - month no].members))

which works perfectly, however it is the previous month to this that I am struggling with.

Can anyone help please? Thankyou.

update:

I am using this mdx as a calculation inside SSAS to then use as a filter for the dataset inside ssrs.

My results are currently:

Contract Name   Question      TblFct     Month    Year
-------------    -------      ------     -----    ---
New Homes       How Many      600        Dec      2014

This is the number for the last month that has data in, i.e. December 2014. I am aiming to have another dataset that is automatically filtered by the same criteria but instead of the last non empty month, I am looking for the month preceeding that. I had looked at the lag function but haven't been able to make it stick for some reason.

My comparison dataset should yield:

Contract Name   Question      TblFct     Month    Year
-------------    -------      ------     -----    ---
New Homes       How Many      450        Nov      2014

Could you please add an example of content and expected result? The very first solution is to use LAG function (or TAIL with Count > 1 parameter), but I'm not sure due to lack of information. — Alex Peshik
Alex, please see the updated question below. Thankyou in advance for your help. — user3735855
I believe you might have posted answer instead of question update. — BI Dude
Thanks BI Dude - unfortunately the "Add Comment" box was too small to hold the amount of text for the update. Can you help with the question? Thanks, Paul. — user3735855

Alex Peshik Alex Peshik · Accepted Answer · 2015-01-06T15:56:09

The quickest solution is to use HEAD function before TAIL, but this is acceptable only in case of non-empty value in this period:

head(tail(nonempty([service date].[quarter no - month no].members),2),1)

But actually even your first solution for the last month may fail if you give users an opportunity to filter data by some dimensions in further SSRS reports with this kind of filters. As for my previous experience, it's better to set up month's parameter 'Last' as additional column/property and after that: as additional dimension hierarchy of current [service date] dimension.

In case of fully-dynamic behavior (when last month differs from report to report), please specify what is acceptable if prev. month is empty:

we need to show empty/zero value
we need to take previous non-empty member (this way it's implemented above)

ssas mdx query for previous previous month

1 Answers