none
2k8r2: dynamic date dim attribute. Financial year including last month of prev FY

    Question

  • Hi All,

    I want to add an attribute to my date dimension that dynamically returns a set of the months in the selected FY + the last month of the previous FY. Is this possible?

    Month Fin Year Fin Year inc Prev Month
    201301 FY 2013 FY 2013
    201302 FY 2013 FY 2013
    201303 FY 2013 FY 2013
    201304 FY 2013 FY 2013
    201305 FY 2013 FY 2013
    201306 FY 2013 FY 2013/FY 2014
    201307 FY 2014 FY 2014
    201308 FY 2014 FY 2014
    201309 FY 2014 FY 2014
    201310 FY 2014 FY 2014
    201311 FY 2014 FY 2014
    201312 FY 2014 FY 2014
    201401 FY 2014 FY 2014
    201402 FY 2014 FY 2014
    201403 FY 2014 FY 2014
    201404 FY 2014 FY 2014
    201405 FY 2014 FY 2014
    201406 FY 2014 FY 2014/FY 2015
    201407 FY 2015 FY 2015
    201408 FY 2015 FY 2015
    201409 FY 2015 FY 2015
    201410 FY 2015 FY 2015
    201411 FY 2015 FY 2015
    201412 FY 2015 FY 2015

    ...

    so if I filter to FY 2014 I want 201306->201406 to be selected. And if i filter to FY 2015 I want 201406->201506 to be selected. Notice how 201406 (the last month of the fin year) is included in both

    Thanks


    Jakub @ Adelaide, Australia


    • Edited by jakubk Monday, July 14, 2014 5:44 AM
    Monday, July 14, 2014 5:43 AM

All replies

  • Hi Jakubk,

    According to your descrption, you want to add an attribute to the dimension that dynamically returns a set of the months in the selected FY + the last month of the previous FY, right?

    In this case, we can achieve it in MDX query. I have tested it on my local environment, the query below is for you reference.

    WITH MEMBER [MEASURES].[PARENTMEMBER]
    AS 
    IIF([Date].[Calendar].CURRENTMEMBER.PARENT.NAME = [Date].[Calendar].PrevMEMBER.PARENT.NAME,
    [Date].[Calendar].CURRENTMEMBER.PARENT.NAME,[Date].[Calendar].CURRENTMEMBER.PARENT.NAME + '/' + [Date].[Calendar].PrevMEMBER.PARENT.NAME)
    
    SELECT {[MEASURES].[PARENTMEMBER]} ON 0,
    [Date].[Calendar].[Calendar Semester].MEMBERS ON 1
    FROM [Adventure Works]

    Results.

    Regards,


    Charlie Liao
    TechNet Community Support

    Wednesday, July 16, 2014 3:15 AM
  • Hi Charlie,

    not exactly. the displayed value should only be "FY yyyy" (this is for use in the end user reporting program/excel slicers etc)

    When the user selects FY 2014 as the filter, the months should filter to 201306->201406. When the user selects FY 2015, the months should filter to 201406->201506. Notice how 201406 appears in both cases.


    Jakub @ Adelaide, Australia

    Wednesday, July 16, 2014 3:27 AM