Skip to Content
Nov 19, 2018 at 02:11 PM

Calculated dimension to check posting date vs todays date


Hi Community,

We are struggeling to set up a user friendly story in regards to filter settings when it comes to Actual vs Budget or Actual vs Previous Year comparissons.

Hence we want to show today (19.11.2018) figures only up to the last fully reported period = October; the dynamic "current" date functions are not applicable for us. We are currently working with a substring formula based on fiscal year /period and additional filters for the years:

As this is causing a bad performance of the story and an even worse user experience (too complex to adjust every month for the end-users) I thought of creating a calculated dimension checking if the date of the transaction data < today's date, giving back a result "false" or "true".
A formula similar to this would maybe help to solve the problem:

Closed Month Current Year = if(and([fisc/per].[Year]=[Actual Year];([postingperiod]<=[Actual Month]-1);"true";"false")

I could then use this dimension as a filter for the reporting period.
Does anyone have experiences with similar issues and can help me setting up the formula as I'm not experienced with the Calculation Editor and the syntax used.

Thanks in advance,