Cubeset (Date Range) - Members that don't exist

[Dtex20]

Hi Guys,

I've got a question regarding the CUBESET Function.

=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Sum of Sales Price Ex Tax]","[Orders].[CON Flag].&[1]",CUBESET("ThisWorkbookDataModel","[Orders].[Created Date].&["&$Q$2&"]:[Orders].[Created Date].&["&$R$2&"]"))

This is my current code that works great, if the date exists:

Q2 = 2019-08-23T00:00:00
R2 = 2019-08-27T00:00:00

My specific problem is, that i want to add a range of dates in which a date member might not exist and i don't want it to return #N/A.

So my data goes up to the 2019-08-27, i want to be able to put a date range that would be in the future and it to return the correct information or until that point regardless if that date exists e.g. 2019-08-29. So the equivalent to a SUMIFs "<", i guess.

Is this possible within the CUBESET function, or would i have to approach this a different way?

I was originally filtering pivot tables through macro's, but i was hoping to get rid of this method and do it all through cube formula's which I've just discovered.

I'm open to any suggestions.

Thanks!

 

[RossTattersall]

A little inelegant, but couldn't you just use an IFERROR to return the data at 2019-08-27 when that formulae gives an error?

 

[Dtex20]

A little inelegant, but couldn't you just use an IFERROR to return the data at 2019-08-27 when that formulae gives an error?

Hi,

No, because i have the problem the other way around as well.

I may have no data that exists on the 2019-08-25 but data that exists on 2019-08-27.. it makes the whole forumla #N/A because it's looking for exact members.

I want to mimic a simple SUMIFS ">=", "<", formula date range. if that is possible, because the dates are completely dynamic it could be 3 days, 2 weeks, 2 months.

Thanks