Hi All I'm trying to calculate Year to Date values using the Query Designer in SSRS for a cube. These are the YTD values I would like to achieve: Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Jan | Feb | Mar |YTD Financial Year 2010/11 3 | 2 | 6 | 8 | 4 | 9 | 1 | 5 | 4 | 6 | 7 | 4 |23 Financial Year 2011/12 5 | 4 | 5 | 4 | 7 | | | | | | | |25 As you can see the YTD column for 2010/11 is 23 and not 59. This is because I only want to sum the periods I have available in 2011/12, which is from Apr - Aug. In SQL i would do this by doing: -- FM is Financial Month, April being M1 and August being M5 DECLARE @FM AS INT = 5 SELECT Financial_Year , LEFT(Financial_Month,3) AS Financial_Month_Txt , CASE WHEN RIGHT(Financial_Month_UID ,2 ) <= @FM THEN COUNT (*) ELSE 0 END AS Counts FROM dbo.myTable Does anyone know how I can achieve this in a SSRS connecting to a SSAS cube? Many thanks in advance. Shaheen

Hi There You can create a matrix report in order to achieve this requirement. Please put your months(periods) in column group and year inside your row group and add total after column group you are done. If you have any questions please do ask. ManyThanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful

Hello, Thanks for your response but I don't think you understood my initial problem; therefore I shall explain again: I have two Financial Years: 2010/11 and 2011/12. I have all month's data for 2010/11 and only data for up until October for 2011/12. Because i dont have all the data for 2011/12 I only want to add the values for periods where I have values for both financial years, for example: 2010/11: apr + may + jun + jul + aug + sep + oct (i dont want to add nov, dec, jan, feb, mar because even though i have this data 2010/11, I dont have it for 2011/12 and therefore I don't want to include these in the totals). 2011/12: apr + may + jun + jul + aug + sep + oct (i dont have data for nov, dec, jan, feb, mar at the moment so it wont be included in totals anyway). I know how to do this using SQL (see code below) but I do not know how to do this when creating a report using a SSAS connection in a SSRS report. To clarify further, see image below: This is the dataset you can use to replicate the report above: DECLARE @FM AS INT = 7; WITh cte AS (SELECT '2010/11' AS FY, 'Apr - 10' AS FM, 5 AS Month_Total, 1 AS Ord UNION ALL SELECT '2010/11' AS FY, 'May - 10' AS FM, 4 AS Month_Total, 2 AS Ord UNION ALL SELECT '2010/11' AS FY, 'Jun - 10' AS FM, 8 AS Month_Total, 3 AS Ord UNION ALL SELECT '2010/11' AS FY, 'Jul - 10' AS FM, 9 AS Month_Total, 4 AS Ord UNION ALL SELECT '2010/11' AS FY, 'Aug - 10' AS FM, 3 AS Month_Total, 5 AS Ord UNION ALL SELECT '2010/11' AS FY, 'Sep - 10' AS FM, 1 AS Month_Total, 6 AS Ord UNION ALL SELECT '2010/11' AS FY, 'Oct - 10' AS FM, 9 AS Month_Total, 7 AS Ord UNION ALL SELECT '2010/11' AS FY, 'Nov - 10' AS FM, 4 AS Month_Total, 8 AS Ord UNION ALL SELECT '2010/11' AS FY, 'Dec - 10' AS FM, 11 AS Month_Total, 9 AS Ord UNION ALL SELECT '2010/11' AS FY, 'Jan - 11' AS FM, 8 AS Month_Total, 10 AS Ord UNION ALL SELECT '2010/11' AS FY, 'Feb - 11' AS FM, 6 AS Month_Total, 11 AS Ord UNION ALL SELECT '2010/11' AS FY, 'Mar - 11' AS FM, 7 AS Month_Total, 12 AS Ord UNION ALL SELECT '2011/12' AS FY, 'Apr - 11' AS FM, 12 AS Month_Total, 1 AS Ord UNION ALL SELECT '2011/12' AS FY, 'May - 11' AS FM, 13 AS Month_Total, 2 AS Ord UNION ALL SELECT '2011/12' AS FY, 'Jun - 11' AS FM, 10 AS Month_Total, 3 AS Ord UNION ALL SELECT '2011/12' AS FY, 'Jul - 11' AS FM, 7 AS Month_Total, 4 AS Ord UNION ALL SELECT '2011/12' AS FY, 'Aug - 11' AS FM, 2 AS Month_Total, 5 AS Ord UNION ALL SELECT '2011/12' AS FY, 'Sep - 11' AS FM, 1 AS Month_Total, 6 AS Ord UNION ALL SELECT '2011/12' AS FY, 'Oct - 11' AS FM, 2 AS Month_Total, 7 AS Ord ) SELECT FY, FM, Month_Total, Ord , CASE WHEN Ord <= @FM THEN SUM(Month_Total) ELSE 0 END AS YTD_Counts FROM cte GROUP BY FY, FM, Month_Total, Ord ORDER BY Ord I hope this clarifies my problem and hope someone can help me with this issue. Many thanks in advance, Shaheen

Hello Is nobody able to help me? :( Perhaps its needs moving in the SSAS Forum?

Hi there One thing which I can suggest you please define a hidden parameter which will control the maximum period for your YTD calculation and then put expression like this =Sum(iif(Fields!Financial_Period.Value<=Parameters!PeriodHidden.Value,Fields!Period_Actual.Value,0)) =Sum(iif(Fields!Financial_Period.Value<=Parameters!PeriodHidden.Value,Fields!Period_Actual.Value,0)) Where Financial_Period is your dataset field and PeriodHidden is your paarmeter and it will only sum where the avlues of your period is less then or equal to the parameter If you have any question please let me know. Many Thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful

Hello Syed, thanks for your response again. What you suggested is fine and works in a relational query, just like how I have suggested in my code above (extract of it below): CASE WHEN Ord <= @FM THEN SUM(Month_Total) ELSE 0 END AS YTD_Counts I believe there is a MDX function called ParallelPeriod which I should use but not exactly sure how to do this. Also, I am not sure how to assign the Maximum Value of a Month into a Variable when extracting from a SSAS dataset. I know how to do this when its a relational dataset. Shaheen

Hello Shaheen, The function ParallelPeriod is to a member from a prior period in the same relative position as a specified member. For example, ParallelPeriod([Date].[Calendar].[Month], 12,[Date].[Calendar].[Month].&[2006]&[8]) It will return the member [Date].[Calendar].[Month].&[2005]&[8] directly. I think it does not meet your requirement to calculate the sum in a period. If we want to achieve this goal, we need to use range operator (:) to calculate the sum value. Here is a sample to calculate the latest 3 months SUM. WITH MEMBER [Date].[Calendar].[Latest3Month] AS SUM (ParallelPeriod([Date].[Calendar].[Month], 2,[Date].[Calendar].[Month].&[2006]&[8]) : [Date].[Calendar].[Month].&[2006]&[8]) SELECT NON EMPTY{ [Measures].[Sales Amount], [Measures].[Standard Product Cost], [Measures].[Tax Amount] } ON ROWS, NON EMPTY{ {(ParallelPeriod([Date].[Calendar].[Month], 6,[Date].[Calendar].[Month].&[2006]&[8]) : [Date].[Calendar].[Month].&[2006]&[8])}, [Date].[Calendar].[Latest3Month] } ON COLUMNS FROM [Adventure Works] Hope my answer is helpful to you. Regards, EdwardEdward Zhu TechNet Community Support