Parameterised Year To Date
Hi, is it possible to use a parameter to drive Year to Date for current year and previous year?
My intention is for the user to select a month e.g. May 2022, this populates two lists. Current YTD Jan to May 2022, and Previous YTD Jan to May 2021.
The lists can then be used in new measures.
Below is example code for un-parameterised Year to Date based on today's date.
strToMember("[Date].[Date Month].["
+ format(DateSerial(Year(Now()), 1, 1),"MMMM ")
+ CStr(Year(DateSerial(Year(Now()), 1, 1)))+ "]")
:strToMember("[Date].[Date Month].["
+ format(NOW(),"MMMM ")
+ CStr(year(NOW()))+ "]")
4 replies
-
Hi Ben,
From your code I see you are using SSAS.
Can you tell me what attributes / hierarchies you have available in your Dates dimension? I'm guessing you have a Full Month, i.e. something like Feb 2022 as a member.
Do you have any hierarchies defined, such as Year / Quarter / Month / Date?
Knowing your model structure will help enormously in how we approach the problem you have posed.
Thanks,
Ian
-
Hi Ian,
All of our key date fields have the same structure. A hierarchy and the individual fields.
Thanks,
Ben
-
Hi Ben,
Ok, great!
We could use either the hierarchy or the full year month attribute. I tend to prefer using hierarchies as they give more flexibility in handling and navigating the data in our calculations as well as giving the user options in drilling down or up.
My demo database has a similar hierarchy made up of Year, Quarter, Month and Date.
First thing we need to do is create a parameter using the Month level of our hierarchy. Once the hierarchy is selected in the parameter dialogue, right click and choose the Months level to create a parameter that contains all the year months:
Then we create our YTD month lists. Drag on the YTD block in the List editor of Formulate and select the hierarchy, then the parameter:
The parameter is defaulting to my last month (December 2016) so the preview shows the YTD list for December 2016, i.e. all my 2016 months.
Duplicate the YTD List.
Click on the blue "selected" chip to show the selected parameter:
You'll see an "*fx" option on the right. Click on this to bring up the functional selections dialogue:
Check the Lag option and enter 12. This has the effect of selecting the year month 12 places before the value selected by the parameter. I.e. if the parameter selected is December 2016, the Lag function selects the year month 12 months prior, December 2015. You can see this in the preview:
Save as Previous Year YTD.
You now have two lists driven by the same parameter. One will be the YTD list of months for the selected month year, the second will be the YTD list of months for the previous year. You can then use these to create whatever measures you need.
Hope that helps.
Ian
-
Hi Ian,
Works perfectly and thank you for the detail you provided. I changed the YTD lists to aggregates so they will work fine with premium and I will soon have a dynamic caption working with a top N.
Slight variation on what you did. My cube has an annoyance in that they have dates going to 2024, so I have created a source list where I can set the date range and sort order. YTD only accepts hierarchies for me. Code below for others tackling sorting lists.
{ORDER({ { [GAAP Date].[GAAP Hierarchy].[GAAP Month].&[200201] : strToMember("[GAAP Date].[GAAP Hierarchy].[GAAP Month].[" + format(NOW(),"MMMM ") + CStr(year(NOW()))
+ "]") } },[GAAP Date].[GAAP Hierarchy].CURRENTMEMBER.MEMBER_KEY,DESC)}Ben
