MDX Filter data set on Current Year

Question

MDX Filter data set on Current Year

Géraldine 0

Hi there,

I’m a very beginner with MDX scripting.

I try to find a way to filter a dataset on the current year, in a MDX query.

My query is working well when I use à constant such as « 2023 », but no data are returned if I use à variable such [Measures].[Current Year] (as Year(Now())).

Can you help going through this ?

Thx ! G.

Alexei Stoyanovsky 3,416 Reputation points

2023-08-25T06:14:18.2766667+00:00

Post your working query with constant '2023', or at least its relevant part

Géraldine 0

First is working well (constant), second doesn't return any value (using "WITH MEMBER [Measures].[CurrentYear] as 'YEAR(Now())'" as variable).

Filter([Cutoff Month].[Cutoff Year].[Cutoff Year].ALLMEMBERS, [Cutoff Month].[Cutoff Year].CurrentMember.Name ="2023" )

Filter([Cutoff Month].[Cutoff Year].[Cutoff Year].ALLMEMBERS, [Cutoff Month].[Cutoff Year] = [Measures].[CurrentYear]) or Filter([Cutoff Month].[Cutoff Year].[Cutoff Year].ALLMEMBERS, [Cutoff Month].[Cutoff Year].CurrentMember = [Measures].[CurrentYear])

I found the solution :

Filter([Cutoff Month].[Cutoff Year].[Cutoff Year].ALLMEMBERS, [Cutoff Month].[Cutoff Year].CurrentMember = StrToMember("[Cutoff Month].[Cutoff Year].&["+Format(now(), "yyyy")+"]")))}

Thank you ;)

Alexei Stoyanovsky 3,416 Reputation points

2023-08-25T10:30:22.3633333+00:00

Just StrToMember("[Cutoff Month].[Cutoff Year].&["+Format(now(), "yyyy")+"]"), optionally wrapped in curly braces if you technically need a set, seems a more straightforward way of doing it.

Your answer

Alexei Stoyanovsky 3,416 Reputation points

2023-08-25T06:14:18.2766667+00:00

Post your working query with constant '2023', or at least its relevant part
Géraldine 0 Reputation points

2023-08-25T10:08:50.7766667+00:00

First is working well (constant), second doesn't return any value (using "WITH MEMBER [Measures].[CurrentYear] as 'YEAR(Now())'" as variable).

Filter([Cutoff Month].[Cutoff Year].[Cutoff Year].ALLMEMBERS, [Cutoff Month].[Cutoff Year].CurrentMember.Name ="2023" )

Filter([Cutoff Month].[Cutoff Year].[Cutoff Year].ALLMEMBERS, [Cutoff Month].[Cutoff Year] = [Measures].[CurrentYear]) or Filter([Cutoff Month].[Cutoff Year].[Cutoff Year].ALLMEMBERS, [Cutoff Month].[Cutoff Year].CurrentMember = [Measures].[CurrentYear])

I found the solution :

Filter([Cutoff Month].[Cutoff Year].[Cutoff Year].ALLMEMBERS, [Cutoff Month].[Cutoff Year].CurrentMember = StrToMember("[Cutoff Month].[Cutoff Year].&["+Format(now(), "yyyy")+"]")))}

Thank you ;)
Alexei Stoyanovsky 3,416 Reputation points

2023-08-25T10:30:22.3633333+00:00

Just StrToMember("[Cutoff Month].[Cutoff Year].&["+Format(now(), "yyyy")+"]"), optionally wrapped in curly braces if you technically need a set, seems a more straightforward way of doing it.

Share via

MDX Filter data set on Current Year

Your answer