Calculating the number of days in a time dimension node - using Grand Total
I need to know the number of days in a time measurement period to calculate the weighted averages. I use the following expression on a calculated measure to get the number of days in the current dimension:
Count(
Descendants(
[Date].[Calendar].CurrentMember,
[Date].[Calendar].[Date Key]
)
)
This works great for all drilling situations, but it doesn't work for Grand Total when I have a filter. I suspect CurrentMember is not working in this situation. It always returns the total number of days in my data. To illustrate, the measure with the above formula is aggregated in BIDS as follows
because my fact data starts in 1984 and there are 11100 days in the time dimension. How can I change the formula so that the filter is taken into account in the aggregation? Users can go to the "Day" level. Here is the Excel pivot table:
source to share
I believe this is a classic case of "Multiselect Issue with Calculated Members". I found a good summary here . I have a solution that works in Excel. It does not calculate the corresponding BIDS amounts, but that is not a problem in this case.
I am using the following dynamic set (this is the Script view in the Calculations tab in the cube editor):
CALCULATE;
CREATE MEMBER CURRENTCUBE.[Measures].[Days In Period Count]
AS Count(Existing [Filtered Date]), VISIBLE = 1 ;
CREATE DYNAMIC SET CURRENTCUBE.[Filtered Date]
AS [Date].[Calendar].[Date Key] ;
My interpretation is that SSAS usually calculates the aggregate in advance and that is [Date]. [Calendar] .CurrentMember is meaningless at the Grand Total level outside of the context of the user's granularity and filter action. Using dynamic set, I force SSAS to compute the aggregate dynamically. There is a theoretical performance hit, but with my cube size I didn't notice much difference.
BIDS then shows the following totals, so far nothing has changed:
But Excel has the correct totals, which reflect only the filtered date coordinates (in this case, only Q1 2012 and all of 2013):
source to share
If you need to filter items from [Date]. [The calendar]. [Key Level] You can traverse descendants with an actual filter>
WITH
MEMBER [Measures].[a] AS
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
FROM [Adventure Works]
source to share