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

Dtex20

Board Regular
Joined
Jan 29, 2018
Messages
50
Hi Guys,

I've got a question regarding the CUBESET Function.

Code:
=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!
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,880
Messages
6,175,157
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top