Datevalue calculation in MDX query

Question

I want to create a dynamic query which updates each day.

So to filter on todays report I use

[Report Date].[Report Date].&[4226]

The 4226 is coming from:

=DATEVALUE("28-07-2017")-38718 or =TODAY()-38718 (convert to number)

38718 is just an arbitrary number to get the correct date from the cube.

EDIT:

Here is my current query:

SELECT NON EMPTY { [Measures].[Price FC] } ON COLUMNS
 FROM ( SELECT ( -{ [Agency].[Nationality - Consortium - Agency].[Nationality].&[111], 
                    [Agency].[Nationality - Consortium - Agency].[Nationality].&[116], 
                    [Agency].[Nationality - Consortium - Agency].[Nationality].&[242], 
                    [Agency].[Nationality - Consortium - Agency].[Nationality].&[134] } ) ON COLUMNS 
FROM ( SELECT ( { StrToMember("[Report Date].[Report Date].&[" + Str(DateValue(Format(Now(), "dd-MM-yyyy")) - 38718) + "]") } ) ON COLUMNS
FROM ( SELECT ( { [Market].[Market].[Market].&[103] } ) ON COLUMNS
FROM ( SELECT ( { [Travel Type].[Travel Type].&[101],
                  [Travel Type].[Travel Type].&[102],
                  [Travel Type].[Travel Type].&[103] } ) ON COLUMNS
FROM ( SELECT ( { [Departure Date].[Year].&[2017] } ) ON COLUMNS
FROM [Booking]))))) 
WHERE ( [Departure Date].[Year].&[2017], 
        [Travel Type].[Travel Type].CurrentMember,
        [Market].[Market].[Market].&[103], 
        StrToMember("[Report Date].[Report Date].&[" + Str(DateValue(Format(Now(), "dd-MM-yyyy")) - 38718) + "]") )

But it says that there is no column detected in the statement. I have also tried different date formats, any ideas?

Answer 1

Following the tips from this thread:

VBA Date as integer

I used CDbl instead of Datevalue, which gave me the desired result!

StrToMember("[Report Date].[Report Date].&[" + Str(Int(CDbl(Now()) - 38718)) + "]")