Use a formulate to set the default parameter value

Hi, I have a parameter value which allows the user to select the GAAP Accounting Period, at the moment to make it easier i have a list which goes from 199901 to Prior month close (202304), and select the last position. 


I want to look at the unwritten positions as well as an option, if I bring in all the account month periods it will go to 202504 or something similar, so i can't see a way to default to 202304.




3 replies

    • Senior Director of Product
    • Ian_Macdonald
    • 1 yr ago
    • Reported - view

    Hi Nick,

    As the default month (last month) will vary as the months go by, the options for the default parameter value don't let you dynamically change the default value.

    The best I can think of for now is to create a custom member on the months hierachy that is dynamically set to the previous month based on today's date. Call this member Last Month:

        StrToMember([Date Range].[date full month name], DateFormat(Date(),"MMM yyyy")).lag(1)

    Date() returns today's date, DateFormat formats the date to match your YearMonth hierarchy, in my case in the form of three character month, space, year. In your case in will be "yyyyMMM". Then map that date to your YearMonth hierarchy using StrToMember.

    Then create a List ordered by selection, making sure you pick the custom member Last Month first, then all the other months.

    Use this list to populate your Parameter:

    And set the default to First:

    When the parameter is used, it will default to Last Month which will give you the right values as you can see here, where I have taken the default parameter value, Last Month and shown it against Apr 2023, last month form today's date showing the same value returned.:


    It does mean the user will have to scroll down or search to get the YearMonth close to Apr 2023, but your default will be correct. I haven't yet found a way to default to Last Month dynamically showing the actual member, in this case Apr 2023.

    Hope that helps though.


      • Nicholas_Campbell
      • 1 yr ago
      • Reported - view

      Ian Macdonald Will give it a a try this evening and see how I get on. Thanks , I had assumed I would be asking this on a Monday , not having an answer within 5 hrs! Much appreciated :)

    • Michael_Raam.1
    • 1 yr ago
    • Reported - view


    Another option you may consider is to create a set with an order that will bring the needed default item to be the 1st item in the set.

    Use the set as the source for the slicer / parameter and set the default value to be the 1st item ( by order ).

    This can provide both the dynamic logic and the original names.

Content aside

  • Status Answered
  • 1 yr agoLast active
  • 3Replies
  • 66Views
  • 3 Following