locked
Dates Between in a cubevalue function RRS feed

  • Question

  • Hi,

    I'm trying to calculate a value based on four months data. These four months are not fixed and are linked to a slicer.

    In Short I have created a measure that returns an average. If you select one month in the slicer, it returns the average for that month. If you select 5 months (Jan, Feb...May) in the slicer it calculates the 5 month average.

    What I'm trying to do it add this functionality in a cube vale.

    I've tried different ways which i'll post below. I've also created a linked table with two dates (four months apart) based on the slicer. I was hoping to use the datesbetween function but that didnt work.

    Any ideas?

    Attemp1: =CUBEVALUE("PowerPivot Data","[Measures].["&TEXT(B$64,1)&"]","{[cal].[DateKey].[All].["&TEXT($B$63,"yyyy/mm/dd")&"],[cal].[DateKey].[All].["&TEXT($C$63,"yyyy/mm/dd")&"],[cal].[DateKey].[All].["&TEXT($D$63,"yyyy/mm/dd")&"],[cal].[DateKey].[All].["&TEXT($E$63,"yyyy/mm/dd")&"]}","[bb].[sgmnt_nm].[All].["&TEXT($A68,1)&"]")

    Attempt2: =CUBEVALUE("PowerPivot Data","[Measures].["&TEXT(B$64,1)&"]","[cal].[DateKey].[All].{["&TEXT($B$63,"yyyy/mm/dd")&"],["&TEXT($C$63,"yyyy/mm/dd")&"],["&TEXT($D$63,"yyyy/mm/dd")&"],["&TEXT($E$63,"yyyy/mm/dd")&"]}","[bb].[sgmnt_nm].[All].["&TEXT($A68,1)&"]")

    Thanks in advance


    Concatch

    Tuesday, April 23, 2013 9:27 AM

Answers

  • Michael,

    In which case you need to adjust your measure to look at the month selected in the slicer as well as the prior 3 months. Maybe something like:

    = CALCULATE( 
                AVERAGEX(Cal[Month],[Sum Values]),
                FILTER(ALL(Cal),
                            Cal[Month] >= MAX(Cal[Month])-3&&
                            Cal[Month] <= MAX(Cal[Month])
                       )
                )

    You would also probably want to package the thing in a HASONVALUE() to ensure your user can't screw things up by selecting more than one month.

    Hope this gives you an idea, post back some more details of your attempted calculation if you are struggling.

    Jacob


    • Edited by Jacob Barnett Tuesday, April 23, 2013 11:41 AM
    • Proposed as answer by Jacob Barnett Thursday, April 25, 2013 11:07 PM
    • Marked as answer by Elvis Long Wednesday, May 1, 2013 7:35 AM
    Tuesday, April 23, 2013 11:26 AM

All replies

  • Sorry if I am totally missing the point but you can quite happily reference the slicer in the CUBE formula.

    =CUBEVALUE("ThisWorkbookDataModel","[Measures].[Sum Values]",Slicer1)

    Would return a measure called [Sum Values] filtered by a slicer called 'Slicer1'. If the measure is an average and the slicer contains your months I woud have thought this would serve your purpose.

    Jacob


    Tuesday, April 23, 2013 10:21 AM
  • Hi Jacob,

    Thanks for the post, I am aware that you can reference the slicer. What I actually need is the selected slicer value as well as the prior 3 months. In short the user will only select one month on the slicer, I need the cubevalue to actually reference the month the user selected as well as the prior 3 months ie the same result that would have happened if the user selected all four months on the slicer.

    I know its silly as the user could just select 4 months in the slicer however for this particular dashboard the user only selects one month at a time.

    Michael


    Concatch

    Tuesday, April 23, 2013 11:13 AM
  • Michael,

    In which case you need to adjust your measure to look at the month selected in the slicer as well as the prior 3 months. Maybe something like:

    = CALCULATE( 
                AVERAGEX(Cal[Month],[Sum Values]),
                FILTER(ALL(Cal),
                            Cal[Month] >= MAX(Cal[Month])-3&&
                            Cal[Month] <= MAX(Cal[Month])
                       )
                )

    You would also probably want to package the thing in a HASONVALUE() to ensure your user can't screw things up by selecting more than one month.

    Hope this gives you an idea, post back some more details of your attempted calculation if you are struggling.

    Jacob


    • Edited by Jacob Barnett Tuesday, April 23, 2013 11:41 AM
    • Proposed as answer by Jacob Barnett Thursday, April 25, 2013 11:07 PM
    • Marked as answer by Elvis Long Wednesday, May 1, 2013 7:35 AM
    Tuesday, April 23, 2013 11:26 AM