Aggregation Type 'Close' with incomplete Date Dimension

Question

We have a schema, were we show the last two months on a daily basis and the last two years on a monthly basis.
To get rid of the days in the date dimension we remove them in a Perspective.

Removing directly in the dimension unfortunately doesn't work, since we would have to give a specific date there (e.g. 2020-10-01) instead of 'two months ago'. And then we would have to change it every month by hand.

Anyway this all works well as long as we don't use for our measures the advanced aggregation type (e.g. Close). As soon as we do that, we get an error if we want to see that measure on a monthly basis more than two months into the past (the daily values are not found, so the last value per day can obviously not be found).

With September 2020 the statement fails:

From October 2020 the statement succeeds:

What is the best way to use these complex aggregation types with incomplete time dimensions?

Answer 1

I found a solution for this use case:

I added a new hierarchy 'day' to the date dimension and used this new hierarchy for the sole purpose of roll_up hierarchy of these measures. This hierarchy won't be filtered, hence there won't be any days missing, but it also won't be used for display in a report. So the user will only see the filtered hierarchy and still the correct number will be shown on month level.

Answer 2

May be you can benefit from this solution. I achieve something 'similar' for my clients:

• create a set with the required periods (based on MDX definitions)
• use this set as master LOV in the master dashboard (in combination with embedded report)
• disable the DATE hierarchy from the application

For example:

create set [periods] as 
 filter( [Time].[Time].[month].members,[time].[time].currentmember.properties('key', typed) > _today()->withDayOfMonth(1)->minusMonths(2))
+ filter( [Time].[Time].[quarter].members,[time].[time].currentmember.properties('key', typed) > _today()->withDayOfMonth(1)->minusMonths(24))

Live example (with different dynamic periods):

(1) = master LOV, with last year loaded, year before, last 12 months, last 24 months, last 4 years, (2) LOV for the detail reports as tabs, (3) the embedded report