...
[Time].[Month].CurrentDateMember
[Time.Weekly].[Week].CurrentDateMember
...
If you want to select a subset of Time dimension level members between specified dates then you can use the DateBetween function to filter level members using a date range expressions. For example, the following expression will return set of last 7 days from [Time].[Day] level members:
Filter(
[Time].[Day].Members,
DateBetween(
[Time].CurrentMember.StartDate,'7 days ago',
'today'
)
)
Conditions
You can write formulas with conditions using IIF and CASE functions. For simple IF / THEN / ELSE type of conditions use IIF(condition, if_true_expression, if_false_expression) (notice that there are two I letters in IIF), for example
...
If there are many conditions then it is easier to use a CASE function, for example
CASE
WHEN [Measures].[Profit] > 1000
THEN 'Big profit'
WHEN [Measures].[Profit] > 0
THEN 'Small profit'
ELSE 'Loss'
END
If all conditions are comparisons of the same expression to different expected values then the other CASE approach can be used, for example
CASE [Time].CurrentMember.Level.Name
WHEN 'Month'
THEN Sum(LastPeriods(3), [Measures].[Store Sales])
WHEN 'Day'
THEN Sum(LastPeriods(90), [Measures].[Store Sales])
END
In IIF and CASE conditions standard comparison operators can be used (=, <, <=, <>, >, >=) as well as AND, OR and NOT operators as well as several specific operators:
...