Know last date from cube

[qvixote]

Hello!

I have a report with a powercube as datasource, with some dimensions including time, with year, month, week and day levels. I need to know the last date (day) that have data on the cube. I've tried with lastChild() dimensional function, but then I've realized that it won't help, cause I've built the cube including all dates in time dimension, even when there's no data for a date, because I need it that way. So, the cube have data between some day in march and some day in june, but usint lastChild() will return the last date in the year(december if asked for months, 2015-12-31 if asked for days...).

Knew that, is there any way to know the max date in the cube's data, not recurring to time dimension?

Thanks.

[qvixote]

I tried with maximum() function, but it gives an error.

[cognostechie]

There is an option to set the 'Current Day' in the cube which is normally set from the Fact data so even if you have future dates in the Date Dimension (which is the same case as mine) the current day from the Time Dimension would be set as the last day of the data, not the last day of the Time Dimension.

I don't have Transformer available right now but I remember it is in the properties of the Data Source ( double click the Fact query in the Data Source window) and
it's a property in one of the tabs.

[qvixote]

Thanks, cognostechie! That was the solution!

[cognos810]

Hello,
Cognostechie's solution is the cleanest one with transformer. Only to add a workaround just in case, if somehow we were not able to change the model, or have a different cube source which does not have this functionality, then you could use the TAIL function, after filtering the date dimension on a fact measure like Sales/Revenue whose values do not exist for future dates.

ITEM(TAIL(FILTER([Date Level], [Sales]<>0)),0) would give you the current date.

-Cognos810

[qvixote]

Hello again!

I'm reopening this topic because there is a problem with the solution that happens sometimes. I used the solution by cognostechie: using 'Current Day' gives the last date with data, and it worked well most of the time.

But sometimes, like today, happens that the cube have last day with data April 12, but 'Current Day' shows April 09. And all aditional data on time dimention, like Yesterday, WTD, etc., have dates only until April 09, but there is data until April 12 in the cube.

I think it's so weird.