Current Month Named Set Calculation?

We use the following MDX:
StrToMember("[Time].[Default].&[" + Format(DateAdd("M", 9, Now()), "yyyy""M""MM") + "]")

It’s easiest to explain from the inside out.

  1. Now() gets the current date/time.

  2. DateAdd("M", 9, Now()) gets the date/time exactly 9 months from now, which is needed because we have a March year-end. So if it is currently 02/2020, this would result in 11/2020 which is the correct fiscal period.

  3. Format(DateAdd("M", 9, Now()), "yyyy""M""MM") represents the resulting date in the MonthKey format used by the Time dimension. For February 2020 this would result in “2020M11”

  4. "[Time].[Default].&[" + Format(DateAdd("M", 9, Now()), "yyyy""M""MM") + "]" is basic concatenation which results in a valid member path. Here we get “[Time].[Default].&[2020M11]”

  5. Finally, wrap that all up in a STRTOMEMBER() function and, if your string is valid it will result in the member.

5 Likes

Thank you, Murray. This was very helpful.

I was able to replicate the current month calculation in model manager but am having limited success when using it on reports due to our use of unique combinations for the columns. For reports with unique combinations, the new calculated field is not available for selection from the Named Set options. When I create a new report that only has one member in the column section of the report, the new Named Set option is available.

Our reports have unique combinations enabled so we can show multiple versions/time frames on a single report:

  1. Current Month Actual

  2. Current Month Budget

  3. Current Month Variance

  4. Year to Date Actual

  5. Year to Date Month

  6. Year to Date Budget

  7. Full Year Budget

  8. Full Year Variance to Date

2 Likes

Yes there are limitations around using named sets together with unique combinations.
One workaround I’ve used is to place multiple data views side by side on the report so that they appear as a single table.

For example in your case you could have three data views, each with the Time dimension as a page-level selection: Current Month, YTD, and Full Year. Each of those would have the versions you want in the columns, and each would have to have the exact same set of rows so they align across.

4 Likes

Thank you again, @murray.mckernan. The calculation is working great.

I am having some issues with the three separate data views, though. The month column doesn’t have the same row set because we are hiding unused rows to make the report shorter. So the first data view is missing a few rows that the full year data views have, so they aren’t lining up. If I uncheck the unused rows box, the one page report becomes a six page report. Do you have another work around hiding up your sleeve? :slight_smile:

1 Like

Yes, but again it involves some MDX. The solution is to create a dynamic named set on the dimension that you want to display on the rows. The named set will be responsible for filtering and ordering of members so that any data view that uses the named set is guaranteed to have a consistent set of rows regardless of the selections being displayed on the report columns.

Example (your dimension names may be different):

  1. The DESCENDANTS function in this example (green) returns all leaf-level members of the “Account” dimension
  2. The NONEMPTY function (blue) filters this list of members to those which have data at the intersection of all the dimension members listed in the perentheses (a “tuple” in MDX terminology). Which members should you use here? See below.
  3. The ORDER function sorts this resulting list according to the value and direction specified. In this case it would sort by the account name, in ascending order.

When defining the tuple that will be used for the NONEMPTY function, a good rule of thumb is to include any dimensions that are in your data views’ columns, plus any dimensions at the page level that you do not want to affect the rows being displayed. Including a dimension in this tuple will make it disregard column or page selections for row filtering purposes. The data itself will still reflect page and column selections, but the rows will remain consistent.

Good luck!

5 Likes

That is a ton more elegant/efficient than the solution I’m working with right now. I’ll have to play with that in our setup and see what happens.

Thank you @murray.mckernan

@murray.mckernan

If I’m reading the code correctly, it looks like you have a time perspective set up for full year. We don’t have that, but it sounds pretty useful. Do you mind sharing the MDX?

1 Like

Is it possible to refer to a Time Nameset in the MDX when using Order?

I am struggling to order the Divisions when I use ‘Current Month’ Time Nameset. However, when I hardcode time (eg 2020M02), the MDX orders the divisions. Can either of you take a look?

ORDER(Descendants([Division].Default.[ALL],LEAVES), ([Account].Default.&[KS312],[Time].Default.&[Current Month]), BASC)

@shan.stephenson Looks like a couple things going on here.

First, the syntax to refer to a named set is simply [Current Month]. Fully qualifying it like [Time].[Default].&[Current Month] causes it to look for an actual member with the key “Current Month”, which doesn’t exist.

Second and more importantly, you can run into trouble if you try to refer to one named set in the definition of another named set. This is because at the database level, all your different named sets are defined in one big MDX “cube script” which is executed every time the cube is processed. Because you can’t control the order of operations within this script, you don’t know which named sets will be defined first during execution and if one named set refers to another which is only defined later in your script, you’ll get an error.

This is why in the example above, I’m not referring to [Current Month] directly, but rather have embedded it’s definition StrToMember("[Time].[Default].&[" + Format(DateAdd("M", 9, Now()), "yyyy""M""MM") + "]")

2 Likes

@jeanette.emery Sorry, I don’t actually have that time perspective set up - I was just guessing as to your setup. What you’re after could probably be achieved using the ANCESTOR function though: https://docs.microsoft.com/en-us/sql/mdx/ancestor-mdx?view=sql-server-ver15

2 Likes

That function did end up working for us. Thank you again!

1 Like

There is Current Month Nameset in the Time Dimension Nameset so the MDX is in fact looking for an active key.

Named set doesn’t work for MDX. You can replace &[CurrentMonth] with CurrentMember, and put Time dimension at Page. Then CurrentMember is the time selected at Page.

Since I need 2 Time Periods, that wont work for what I’m trying to achieve. Instead, I hardcoded
time and will change it every month to plot current Ytd & Prior Ytd data on a chart.

try this MDX to get Prior Month:
([Time Perspective].[Default].&[BASE], [Time].[Default].CurrentMember.LAG(1))

1 Like

So…I went about this the long way around because I had some trouble with MDX (I’m learning for the first time). Here is the workaround:

  1. Set up a Time dimension Named Set “Current Calendar Month” (static calc is fine) with the following formula (Note: if you are not on a calendar fiscal year, you can use the 0 below in the Date Add part of the formula to adjust to your calendar. Murray’s fiscal year starts in Sept, so he puts a 9 there to adjust to make his year start then. We use a calendar year as our fiscal year so I replaced the 9 with a 0).

StrToMember("[Time].[Default].&[" + Format(DateAdd(“M”, 0, Now()), “yyyy”“M”“MM”) + “]”)

  1. Set up a Time dimension Named Set “Current Calendar Year” (static calc is fine) with the following formula. Same as above, except it returns a year YYYY instead of Year-Month YYYY-MM.

StrToMember("[Time].[Default].&[" + Format(DateAdd(“YYYY”, 0, Now()), “yyyy”) + “]”)

  1. I created a calculated version for Full Year using the formula provided by my Prophix implementation support Sahil that results in the full year budget no matter which month is selected:

((Ancestor([Time].[Default].CurrentMember, [Time].[Default].[Year])), [Version].[Default].&[Final], [Measures].[Value])

  1. I used two different views to set up my report using the above formulas. Because a month column is limited to a single month (Feb, for example). It may not include the same accounts as Jan. We had an unbudgeted expense in Jan, so there was an amount in the actual for that month. In Feb, we do not have activity in that account, so it was hiding the row on the report in the month columns, but displaying that row on the YTD column. I tried Murray’s formula and couldn’t figure out how to modify the supplied code to match our setup. What I did instead was create a new Version called “Current Month Placeholder” (calculated type) where I added all versions that might be referenced on the report within a given year (our actuals, final budget, and four projections)

[ACT]+[FINAL]+[Q2-BPROJ]+[Q3-BPROJ]+[Q4-BPROJ]+[Full Year]

  1. Create your report with two views (using the same view as the base). Limit the first view to BASE perspective and make the second view YTD perspective

  2. Link the two views so you can make a selection in one and it will apply to the other. I used this for the members in the pages (project, dept, time) BUT do not link the time perspective. I would recommend hiding the time perspective so the viewer of the report doesn’t get confused by two perspective selections on one report (also, you don’t want them changing perspectives!).

That was a very long/complicated workaround and I promise there is a better way to do it. With my baby MDX skills (and limited time) this was the best I could do. It is working for us (so far) without a hitch (fingers crossed/knock on wood). Hopefully this helped you find a solution to your issue.

2 Likes

Another alternative is to set up a new time perspective:

([Time Perspective].[Default].&[YTD],ParallelPeriod([Time].[Default].[Year],1,[Time].[Default].CurrentMember))

1 Like

Lots of interesting discussion here about MDX formulas.

When we talk to our Prophix support contacts, they caution us against doing things in MDX since it can slow down system performance. Especially for something simple like current month named set. Does anyone see those performance impacts?

1 Like

A thousand thank-yous! I wish I could lift you up on my shoulder to the cheers and applause of an Olympic audience watching a gold medal performance.