Answered by:
YTD/PeriodsToDate for Financial Year

Question
-
Hi
I am trying to debug a calculation we have in our current cube
SUM(PERIODSTODATE( [Date].[Financial Year - Month].[Financial Year]),[Measures].[KPI Value Monthly])
When a user selects the 10th Month in the parameter (January) for the current year the calculation does not work.
From reading the Microsoft https://msdn.microsoft.com/en-us/library/ms146039.aspx - YTD and periodstodate don't work for Financial Year calculations so how can I get around this?
Regards
- Edited by aivoryuk Wednesday, October 12, 2016 1:39 PM
Wednesday, October 12, 2016 1:38 PM
Answers
-
Hi all
the actual issue turned out not to be the MDX - it was actually at the report level as the calendar year field had been dragged into the query which is why it was resetting the PERIODSTODATE count when it got to January.
Regards
Tuesday, October 18, 2016 10:27 AM
All replies
-
Try adding the member from which you wish to sum up to. ie.
SUM(PERIODSTODATE( [Date].[Financial Year - Month].[Financial Year],[Date].[Financial Year - Month].CurrentMember),[Measures].[KPI Value Monthly])
Richard
Wednesday, October 12, 2016 9:49 PM -
Hi aivoryuk,
In MDX, if you only specify level expression in PERIODSTODATE() function, the current member is inferred hierarchy.CurrentMember automatically. I don't how you filter the data with January. Even you don't specify member expression, this function should still work when applying January member on rows.
Please test with Richard's expression. If it still doesn't work, you need to verify if you build your Fiscal hierarchy properly, check corresponding relationships. See AdventureWorks sample below:
Regards,
Simon Hou
TechNet Community SupportThursday, October 13, 2016 1:29 PM -
Hi all
the actual issue turned out not to be the MDX - it was actually at the report level as the calendar year field had been dragged into the query which is why it was resetting the PERIODSTODATE count when it got to January.
Regards
Tuesday, October 18, 2016 10:27 AM