ssas - Calculating the number of days in a time dimension node - with Grand Total -


i need know number of days in time dimension period calculating weighted averages. using following expression in calculated measure obtain number of days in current dimension member:

count(     descendants(         [date].[calendar].currentmember,         [date].[calendar].[date key]     )    ) 

this works fine drill-down situations, not work grand total when have filter. suspect currentmember not work in situation. returns total number of days in data. illustrate, measure above formula aggregated in bids follows

browsing cube in bids

because fact data starts in 1984 , there 11100 days in time dimension. how can change formula filter accounted in aggregation? users can drill down day level. here excel pivot table:

browsing cube in excel

if need filter members [date].[calendar].[date key] level can intersect descendants result actual filter>

with    member [measures].[a]      count     (       intersect       (         descendants         (           [date].[calendar].currentmember          ,[date].[calendar].[date]         )        ,filter         (           [date].[calendar].[date].members          ,           ([measures].[sales amount],[date].[calendar].currentmember) > 10000         )       )     )  select    {[date].[calendar].[calendar year].members} on rows  ,{[measures].[a]} on columns [adventure works] 

Comments

Popular posts from this blog

javascript - DIV "hiding" when changing dropdown value -

Does Firefox offer AppleScript support to get URL of windows? -

android - How to install packaged app on Firefox for mobile? -