24 March 2017

# The dynamic members

Usually we consider dynamic members as calculated measures. [Measures] is a special dimension to get the values result. Nonetheless, we are able to apply the same dynamics to other dimension members. That’s rarely used, but is useful for certain cases. Let me show you few examples.

Clock

There is rather a conceptual than a practical proof: you can return current time with measure.

``````with

member [Measures].[Current Time] as
Format(Now(),'HH:mm')

select [Measures].[Current Time] on 0
from
[BI Fake]``````
 Current Time 13:05

Today, Yesterday, Current Month, etc.

Sometimes you want to create dynamic reports with pure MDX (you may use that within Excel, just press Update button and get the current date data).  For example, in order to return the data for yesterday, today, tomorrow. The best way is to add calculated members to your Calendar hierarchy.

``````with

member [Date].[Calendar].[Today] as
StrToMember('[Date].[Calendar].[Day].&[' + Format(Now(),'yyyyMMdd') + ']')

member [Date].[Calendar].[Yesterday] as
StrToMember('[Date].[Calendar].[Day].&[' + Format(Now(),'yyyyMMdd') + ']').Lag(1)

member [Date].[Calendar].[Tomorrow] as

select [Measures].[Invoice Count] on 0,
{[Date].[Calendar].[Yesterday],[Date].[Calendar].[Today],[Date].[Calendar].[Tomorrow]} on 1
from
[BI Fake]``````
 Invoice Count Yesterday 3793 Today 768 Tomorrow 405

The same way you are able to generate the current month.

``````with

member [Date].[Calendar].[Current Month] as
StrToMember('[Date].[Calendar].[Month].&[' + Format(Now(),'yyyyMM') + ']')

member [Date].[Calendar].[Previous Month] as
StrToMember('[Date].[Calendar].[Month].&[' + Format(Now(),'yyyyMM') + ']').Lag(1)

select [Measures].[Invoice Count] on 0,
{[Date].[Calendar].[Previous Month],[Date].[Calendar].[Current Month]} on 1
from
[BI Fake]``````
 Invoice Count Previous Month 204439 Current Month 177573

Last 3 months

Let’s say you frequently analyze your data slicing the last three month. In this case you have to handle sets (three Month members).
``````with

member [Date].[Calendar].[Current Month] as
StrToMember('[Date].[Calendar].[Month].&[' + Format(Now(),'yyyyMM') + ']')

member [Date].[Calendar].[Previous Month] as
StrToMember('[Date].[Calendar].[Month].&[' + Format(Now(),'yyyyMM') + ']').Lag(1)

member [Date].[Calendar].[Last 3 Month] as
Aggregate(
LastPeriods(
3,
StrToMember('[Date].[Calendar].[Month].&[' + Format(Now(),'yyyyMM') + ']')
)
)

select [Measures].[Invoice Count] on 0,
{[Date].[Calendar].[Previous Month],[Date].[Calendar].[Current Month],[Date].[Calendar].[Last 3 Month]} on 1
from
[BI Fake]``````
 Invoice Count Previous Month 204439 Current Month 177573 Last 3 Month 563572

Conclusion

The OLAP nature isn’t dynamical. First of all, it’s designed to store pre-aggregated data. However, you may found it useful sometimes. My examples are related to time dimensions, but you can use it for your own members depending on your business cases.