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

Answer 1

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

Answer 2

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 Support

Answer 3

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