Help writing a cube function

[shophoney]

Hi I'm trying to write a cube function that has just todays sales.

Currently i have a slicer for month and year.

=CUBEVALUE("ThisWorkbookDataModel","[Measures].[SALES TOTAL NET RETAIL]",Slicer_MONTH1,U$6,Slicer_YEAR1)

But I would like to have just today. IE TODAY()

I've tried this but that brings up a date i have to write into the formula.

=CUBEVALUE("ThisWorkbookDataModel","[Measures].[SALES TOTAL NET RETAIL]","[SALES TABLE].[TRANS DATE].&[2017-07-03T00:00:00]")

Any thoughts?

Thanks

 

[ParamRay]

= CUBEVALUE("ThisWorkbookDataModel",
        "[Measures].[SALES TOTAL NET RETAIL]",
        "[SALES TABLE].[TRANS DATE].&[" & TEXT(TODAY(), "yyyy-mm-ddThh:mm:ss") & "]"
)

 

[shophoney]

= CUBEVALUE("ThisWorkbookDataModel",
        "[Measures].[SALES TOTAL NET RETAIL]",
        "[SALES TABLE].[TRANS DATE].&[" & TEXT(TODAY(), "yyyy-mm-ddThh:mm:ss") & "]"
)

Thank you very much. This will be so helpful.

 

[shophoney]

Hi I wonder if you have any insight in to how to solve this issue.

I have a cube formula with 3 parts. And if any one of the parts does not have values in it. The formula wont work. I end up with #Value! error.

=CUBEVALUE("ThisWorkbookDataModel","[Measures].[1YR AGO OH RETAIL]",'MONTHLY RECAP'!$B217,"[tblLocation].[loc_code].&[1000]",Slicer_YEAR1,Slicer_MONTH1,Slicer_NAME7)-CUBEVALUE("ThisWorkbookDataModel","[Measures].[1YR AGO RETAIL TRANSFERED]",'MONTHLY RECAP'!$B217,"[TRANSFERS].[loc# to].&[1000]",Slicer_YEAR1,Slicer_MONTH1,Slicer_NAME7)+CUBEVALUE("ThisWorkbookDataModel","[Measures].[1YR AGO RETAIL TRANSFERED]",'MONTHLY RECAP'!$B217,"[TRANSFERS].[loc# created].&[1000]",Slicer_YEAR1,Slicer_MONTH1,Slicer_NAME7)


Thanks

 

[Midlander]

Hi I wonder if you have any insight in to how to solve this issue.

I have a cube formula with 3 parts. And if any one of the parts does not have values in it. The formula wont work. I end up with #Value! error.

=CUBEVALUE("ThisWorkbookDataModel","[Measures].[1YR AGO OH RETAIL]",'MONTHLY RECAP'!$B217,"[tblLocation].[loc_code].&[1000]",Slicer_YEAR1,Slicer_MONTH1,Slicer_NAME7)-CUBEVALUE("ThisWorkbookDataModel","[Measures].[1YR AGO RETAIL TRANSFERED]",'MONTHLY RECAP'!$B217,"[TRANSFERS].[loc# to].&[1000]",Slicer_YEAR1,Slicer_MONTH1,Slicer_NAME7)+CUBEVALUE("ThisWorkbookDataModel","[Measures].[1YR AGO RETAIL TRANSFERED]",'MONTHLY RECAP'!$B217,"[TRANSFERS].[loc# created].&[1000]",Slicer_YEAR1,Slicer_MONTH1,Slicer_NAME7)


Thanks

Try wrapping each CUBEVALUE() function with an N() function. This will convert an empty string (as returned by a CUBEVALUE() function without a value) into a zero.

 

[shophoney]

Thanks, that was great. I really needed it for calculations.