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
StrToMember('[Date].[Calendar].[Day].&[' + Format(Now(),'yyyyMMdd') + ']').Lead(1)

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.