Is there a way to calculate a date off of a date parameter?
Hello,
We have an evaluation date parameter and we want to create a prior year end date value for a formula. In other words the user chooses March 2024 for premium and then for the "Prior Year End Premium" formula we want the premium value for December 2023.
Our parameter is a date that is the end or each month, like 3/31/2023, 4/30/2023, etc. We are unable to figure out how to change the date into the end of the prior year, like 12/31/2022.
How do we go about doing this?
Thanks,
Rob Clark
3 replies
-
Hi Rob,
I am not sure if my suggestion presents the type of object you are searching for......but the Custom Column "End of Prior Year" generates the date of the last day of the previous year for any given date (here: "DATUM_AKTIVIERUNG").
I simply create a string by concatening the number of the previous year (or "9998" if the given date is NULL) with "-12-31" and transform this string to a date object.
Something very similar should work with a formula as well - but in this case you will not be able to use the given date and the "End of Prior Year" formula at the same time.
Hope this helps
Michael -
Hi
To calculate it from the parameter do the following:
Add a Member block on your Formulate canvas and select the Parameter (my dates are the start of the month, not the end, but the same thing applies). Click on the little "fx" icon on the selected parameter and add Lag 12. This will return the date 12 month prior (as your dates only contain the last day of each month).
Use the function "FullYear()" to return all the dates in the year defined by the lagged parameter (i.e. all the dates in the year previous to the one selected).
Now we need the last date, so use the function LastElement() to get it:
You can use this to create a new member in the Dates dimension which will always be the last date in the year previous to the year in which the date was selected, or as a tuple with a Measure to return a value, like your premium,
Hope that helps.
Ian