MDX query help

[MorbidFlorist75]

Hi,

I need to amend the following

(SELECT ( [Action date].[Calendar].[Date].&[20230413] : [Action date].[Calendar].[Date].&[20230401] )

so the actual data range is always "execution day minus 7 days : execution day".

I need to see last seven days worth of data on a day report runs basically.

I tried many solutions suggested by Google but can't make this thing work yet. Any help would be greatly appreciated.

Thank you

 

[sityeuropa]

I've no idea what this is, and forgive me if it is completely wrong, but I just tried asking chatgpt, it usually helps when you know a bit about the subject.
Maybe there is something in the below that helps.

WITH MEMBER [Measures].[CurrentDate] AS
    CDate(Format(Now(), "yyyy-MM-dd"))

SELECT
    (
        [Action date].[Calendar].[Date].CurrentMember.Lag(6) :
        [Action date].[Calendar].[Date].CurrentMember
    )
ON COLUMNS
FROM [YourCube]
WHERE [Measures].[CurrentDate]

Let's break down the changes:

1. WITH MEMBER [Measures].[CurrentDate]: This line creates a calculated member named "CurrentDate" within the "Measures" dimension.
2. CDate(Format(Now(), "yyyy-MM-dd")): This expression uses the Now() function to get the current date and time, formats it as a string in the "yyyy-MM-dd" format, and then converts it back to a date using the CDate() function. This ensures that the time part is removed and only the date part is considered.
3. ON COLUMNS: The range of dates is placed on the columns axis.
4. FROM [YourCube]: Replace [YourCube] with the name of your data cube.
5. WHERE [Measures].[CurrentDate]: This line filters the data based on the calculated "CurrentDate" member.
6. [Action date].[Calendar].[Date].CurrentMember.Lag(6): The Lag() function is used to get the member that is 6 positions before the current member (in this case, the current date) in the "Date" level of the "Calendar" hierarchy.

This MDX query selects the range of dates that correspond to the previous 7 days, including the current date, within the "Date" level of the "Calendar" hierarchy in the "Action date" dimension. Note that you still need to specify the desired measures and other dimensions to perform a more meaningful analysis.