0

I would like to get Cumulative Sum from Jan - Dec for each year

Something similar to Below But replace First Month and Last Month with the current year data. I have data from 2014 -2017

Sum( [Ship Date].[Date].CURRENT_MEMBER.FirstMonth : [Ship Date].[Date].CURRENT_MEMBER.LastMonth,[Measures].[Revenue] )

SKenneth
  • 19
  • 11

1 Answers1

0

You may be able to use this structure which I’ve taken from the MSDN YTD page:

WITH MEMBER MEASURES.YTDDEMO AS
AGGREGATE(YTD(), [Measures].[Internet Sales Amount])
SELECT {[Measures].[Internet Sales Amount], MEASURES.YTDDEMO} ON 0,
[Date].[Calendar].MEMBERS ON 1
FROM [Adventure Works]

Here is another alternative:

Aggregate(

     PeriodsToDate( 
           [Date].[Calendar Hierarchy].[Year],
           [Date].[Calendar Hierarchy].CurrentMember 
     ),
     [Measures].[Sales]
 )
whytheq
  • 34,466
  • 65
  • 172
  • 267
  • This didn't work. Am I missing anything Aggregate( PeriodsToDate( [MonthEnd].[Month_End].[Year], [MonthEnd].[Month_End].CurrentMember ), [Measures].[TransAmt] ) – SKenneth Apr 23 '18 at 15:20
  • Month_End is the Hierarchy name and Year,Month,date is the hierarchy order – SKenneth Apr 23 '18 at 15:22