Day Count Between LastTransaction Date and Current Date

i have 4 measure

1. [max transaction date]  calulated
2. [currentmember] - current date (calculated)
3. daycount - measure dericed dimdate

4. [duecount]  [error part]

belllow code works fine these 3 measure.
now issue part

**************************************
member [date].[date].[duecount]
as sum({([date].[date].[max transaction date]): membertostr([date].[date].[currentmember])})

select {([date].[date].[duecount],[measures].[daycount])} on rows
on columns,
[vendor].[vendor].members
on rows
from [vendor ledger]


error msg :

cellordinal 0
value #error calculated member cannot used operand of range operator ( : ).
formatted_value #error calculated member cannot used operand of range operator ( : ).


 
****************************************
but this code works because there no calculated member

member

 

[date].[date].[my range] as 'sum(({[date].[date].&[2005-01-01t00:00:00]: [date].[date].&[2005-01-04t00:00:00]}))'

select

([date].[date].[my range],[measures].[daycount] ) on columns,
[vendor].[vendor].members on rows from [vendor ledger]

 

 

this code works except due count

with member [date].[date].[max transaction date]   tail(filter([date].[date].members, not isempty([measures].[amount]))).item(0).name
  member [date].[date].[currentmember]   strtomember("[date].[date].&["   + format(now(), "yyyy")   + "-"   + format(now(), "mm")   + "-"   + format(now(), "dd")  + "t00:00:00]"   ).name     member [date].[date].[duecount]   sum({([date].[date].[max transaction date]).member_name : membertostr([date].[date].[currentmember]).member_name })    --[date].[date].&[2005-01-06t00:00:00]    select {    ([date].[date].[max transaction date])    ,([date].[date].[currentmember])     --([date].[date].[duecount],[measures].[daycount])    }    on columns,    [vendor].[vendor].members   on rows    [vendor ledger]

 

---

result above code:
2009-03-16 00:00:00 2009-05-08 00:00:00
2008-03-31 00:00:00 2009-05-08 00:00:00
2008-03-31 00:00:00 2009-05-08 00:00:00
2009-02-27 00:00:00 2009-05-08 00:00:00

can 1 me counting [duecount]  (calulated date dimension members count)

thanks
 

hi,

is error message says, ":" not work calculated members
because calculated members not part of hierarchy , cannot define range based on them

simle workaround not use calculated member range operator use value calculated member based on directly

with   member [date].[date].[duecount]   sum({tail(filter([date].[date].members, not isempty([measures].[amount]))).item(0) : strtomember("[date].[date].&[" + format(now(), "yyyy") + "-" + format(now(), "mm") + "-t00:00:00]"},
 <your measure>
)    --[date].[date].&[2005-01-06t00:00:00]  select {  ([date].[date].[max transaction date])  ,([date].[date].[currentmember])   --([date].[date].[duecount],[measures].[daycount])  }  on columns,  [vendor].[vendor].members   on rows    [vendor ledger]

hth,
gerhard

---

- www.pmone.com -

SQL Server  >  SQL Server Analysis Services