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
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:
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
Post a Comment