Let’s say we have the following MDX query to show data for a particular date (in this case the quantity measure of the cube Electricity):
WITH
SET [~ROWS] AS {[Time].[Day].[2014-01-01]}
SELECT
NON EMPTY {[Measures].[Quantity]} ON COLUMNS,
NON EMPTY [~ROWS] ON ROWS
FROM [Electricity]
Works OK:
But what if we want the date to be dynamic, reflecting today’s date?
We can change the MDX to use Mondiran’s CurrentDateMember function:
WITH
SET [~ROWS] AS {CurrentDateMember([Time], """[Time].[Day]""\.[yyyy-mm-dd]")}
SELECT
NON EMPTY {[Measures].[Quantity]} ON COLUMNS,
NON EMPTY [~ROWS] ON ROWS
FROM [Electricity]
Now the date selected is today’s date (Note: date members in the time hierarchy are in the format of yyyy-mm-dd):
Extra tip – rolling date range up to current day
We can also change the MDX query to select a rolling 365-day date range (ending with the current day):
...
SET [~ROWS] AS {CurrentDateMember([Time], """[Time].[Day]""\.[yyyy-mm-dd]").Lag(356):CurrentDateMember([Time], """[Time].[Day]""\.[yyyy-mm-dd]")}
...
Now a rolling 365-day date range is shown:
Analytics Anvil 