locked
YTD/PeriodsToDate for Financial Year RRS feed

  • 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


    • Marked as answer by aivoryuk Tuesday, October 18, 2016 10:27 AM
    • Edited by aivoryuk Tuesday, October 18, 2016 10:27 AM
    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 Support


    Thursday, 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


    • Marked as answer by aivoryuk Tuesday, October 18, 2016 10:27 AM
    • Edited by aivoryuk Tuesday, October 18, 2016 10:27 AM
    Tuesday, October 18, 2016 10:27 AM