Create a measure to return the maximum date about the order in a fact table. (SSAS Multidimensional)

Question

I want to create a measure which return the maximum date about Orders but before the actual day

I will write an example :

My tables here

(In my table Calendar i have the year 2016,2017,2019, and in my Order table, i have an order for 2016 and 2019, I want the last date order but before the actual day (18/05/2017), so i want the Date 01/01/2016).

I have 2 table, a dimension Calendar and a fact table Order.

I was thinking about the function filter, so i search how to use filter in google, and all the solutions i found use 'With' and 'Select'. (I can't use 'With' and 'Select' when i create a measure in SSAS multidimensional).

Hope i will see your advice.

Answer 1

Just like this similar case in adv cube?

[max order date] return the maximum date about [Internet Sales Amount]

with member [max order date] AS
tail(NONEMPTY([Date].[Date].[Date],[Measures].[Internet Sales Amount])).item(0).item(0).PROPERTIES( "name" )

select {[max order date] } on 0  from [Adventure Works]

if yes, then you can create a measure in your cube like this:

Create Member CurrentCube.[Measures].[max order date]
 As  tail(NONEMPTY([Date].[Date].[Date],[Measures].[Internet Sales 
Amount])).item(0).item(0).PROPERTIES( "name" );

if only till current day, then(following is refer to adv cube, you need do some code changes per your cube):

Create Member CurrentCube.[max order date] AS 
Tail
(
  NonEmpty
  (
    {
        Head([Date].[Date].[Date]).Item(0).Item(0)--the first day in your Date dim
      : 
        StrToMember("[Date].[Date].&[" + Format(Now(),"yyyyMMdd") + "]")-- as of current day
    }
   ,[Measures].[Internet Sales Amount]
  )
).Item(0).Item(0).Properties("name") 

IDE to Write, Analyze, Tuning, Debug MDX efficiently (www.mdx-helper.com)