• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 824
  • Last Modified:

Mdx count measures

I want made a measures that count days between two dates, but the first date is same (2007-08-31), but the second is now or the fact's last date .

i have:
count([Time].[Date].&[2007-08-31T00:00:00]:[Time].[Date].&[2009-03-18T00:00:00])

i want :

count([Time].[Date].&[2007-08-31T00:00:00]: now)      -  [b]error[/b]

count([Time].[Date].&[2007-08-31T00:00:00]: Tail(NonEmpty([Time].[Year -  Month -  Date].Members,[Measures].[qty] )).item(0).membervalue )      -[b]  error[/b]

Do you know how I can do it?

thanks
0
rauljimenez
Asked:
rauljimenez
  • 2
1 Solution
 
RWrigleyCommented:
The closingperiod function will give you the "last" member of the specified time level, but that may not be accurate if your cube has forward looking dates:

select
closingperiod ([Date].[Calendar].[Date]) on Rows,
[Measures].[Internet Sales Amount] on Columns
from [Adventure Works]


A more robust solution is to generate a set filtered on a measure that you know only has values for current dates, and then take the last member of that:

select
tail(filter([Date].[Calendar].[Date],[Measures].[Internet Sales Amount] <> NULL),1) on Rows,
[Measures].[Internet Sales Amount] on columns
from [Adventure Works]

A similar approach is to define an attribute flag in your time dimension that will indicate the current day.  However, this is only practical if you're updating the cube every day.
0
 
RWrigleyCommented:
Here's an Adventure works example:
WITH MEMBER [Date].[Calendar].[COG_OQP_USR_Count(Last)] AS 
'
COUNT(
 EXCEPT(
  PERIODSTODATE(
    [Date].[Calendar].[(All)], 
    TAIL(
      FILTER(
       {[Date].[Calendar].[Date].MEMBERS}, 
       NOT ISEMPTY([Measures].[Internet Order Quantity]
      )
     ), 
     1
    ).ITEM(0).ITEM(0)
   ), 
   PERIODSTODATE(
    [Date].[Calendar].[(All)], 
    TAIL(
     FILTER(
      {[Date].[Calendar].[Date].MEMBERS}, 
      NOT ISEMPTY([Measures].[Internet Order Quantity]
     )
    ), 
    1
   ).ITEM(0).ITEM(0).LAG(12)
  )
 ), 
 INCLUDEEMPTY
)', 
SOLVE_ORDER = 4, FORMAT_STRING = "#" 
 
SELECT 
	{[Measures].[Internet Order Quantity]} ON Rows, 
	{[Date].[Calendar].[COG_OQP_USR_Count(Last)]} ON Columns 
FROM [Adventure Works]  

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now