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

# 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
• 2
1 Solution

Commented:
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

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

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

Commented:
``````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
``````
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.