Hi Frédéric WILHELM

Just to clarify, you want the headcount as of the previous DAY, or the headcount of the previous MONTH? 

Do you have an existing Measure of headcount? If you do, is it aggregated as a semi additive measure in your model, i.e. as "Last Child"?

Thanks for the additional information.

Ian

Hi Frédéric WILHELM

From your post and the code you quoted, I deduce that you are working with Analysis Services using MDX.

Analysis Services does not have a DateTime data type. Members of a hierarchy representing Dates and Times are simply the formatted DateTime string, created from the underlying DateTime column from the data source used to build the cube.

What the code is doing is getting the current system Date using the VBA function Now(), then formatting it as a 4 digit string for the Year ("yyyy"), the 2 digit month with leading zeros ("mm") and the time as a straight string and concatenating them together (with "-" between the strings) to match the format of the members of the Cube hierarchy. The MDX STRTOMEMBER() function does the matching and returns the member for today. The .Lag(1) will return the Member for the previous Member, i.e. yesterday.

This the most reliable way to do this and requires the code you have quoted.

There is another way to go about doing this, but I'll need some information from you to check if will work for your cube model.

1. How frequently does your data get updated in your cube?
2. If you create an analysis with Dates on the Rows and your contracts and amendments Measures on the columns, what is the last date displayed that contains values for the measures? Is it today or yesterday?
3. Do you have a Date Hierarchy in your model? For example where you can drill down form Year to Quarter to Month to Date?

LMK and we'll see if we can do this using a different technique that can use the Pyramid graphic tools.

Hope that helps.

Ian

Hi Frédéric WILHELM

Here's a solution using dynamic lists. It relies on the fact that you never have a headcount measure value for a future date.

Create a Formulate list using your model. I'm using a Pyramid model, rather than a SSAS cube, but it is the same for both.

Add a standard list and populate it with all your Dates:

Add a NonEmpty Filter and select your headcount / contract Measure. In my case I'm using Sales (as there is never sales on a future date):

Add a Last filter and set the value to 1 (i.e. you're selecting the last member in the list):

Save your list, in my case I'm calling it "Yesterday".

You can then use the list in your Discover either in the grid or as a filter:

This will give you the latest value in the database for your measure and if the database is updated daily, it will always give you yesterday in the list. It gets a bit more complicated to always get "last month's" last value, as you have to do two things, get the previous month and then find the last value in that month.

There is a type of aggregation in both Pyramid and SSAS models called "semi additive" to handle these types of measures where you want the measure aggregated across dimensions but not Time, where you want the last value instead. Headcount, Cash Balance, Inventory all fall into this category.  Ask you database people if they use this, in which case it makes everything much simpler, just find the previous month and look at the measure as it will have the last value in that month by default.

Hope that helps.

Ian