2/24/2014

MDX AGGREGATE Function

Today's task was to SUM the Month To Date values for a particular Measure in MDX.

To do that we used the AGGREGATE function in MDX:


Aggregate(

            PeriodsToDate( [FiscalDate].[Fiscal Calendar Drilldown].[Fiscal Month],

            [FiscalDate].[Fiscal Calendar Drilldown].CurrentMember),

            ([Measures].[Fuel Gallons Upload])

)

You give it the time period you want to Summarize, in this case, the Fiscal Month from the Fiscal Calendar Drilldown Measure.

From there, you tell it the CurrentMember for the exact location within the Drilldown.

Finally, you supply the Measure to Add, "Fuel Gallons Upload".

This query, goes to the beginning of the Month, adds up all the values, up to and including the Current Member of the Date Hierarchy.

Fun stuff!

No comments:

Post a Comment