none
Last 6 month dimension

    Question

  • Greetings !

    Scenario:

    I have a requirement to create a trend chart to show trend for last six months. So on trend chart, it should reflect previous 6 months (i.e. Jul, Aug, Sep, Oct, Nov, Dec) and value against each month.

    Problem:

    I am using Power view in SharePoint (for multidimensional model) to create this report from SSAS (SQL 2012 SP1) and it doesn't support any custom MDX query to filter data.

    Question:

    1. Is it possible to create such a dimension (dynamic in nature) which can return me last 6 months always? If yes, please show some direction.

    I tried to use time intelligence wizard but it just returns a single value on axis (tried for MTD and just return one value MTD instead of returning multiple dates).

    2. Can I write any DAX query to achieve it?

    Please suggest the solution as we have to deliver project within 1-2 days but we are still clueless about the solution.


    Best Regards,
    Youdhbir

    Blog : http://youdhbir.blogspot.in/

    Saturday, January 11, 2014 9:21 AM

Answers

  • Easiest option is to add an attribute to your "Date" dimension and use the attribute as a slicer in Power View.

    Here is the list of steps you need to follow

    1. In your DSV, add a computed column named "Last Six Months"

    2. set the expression as "If Date < 6 months then 'No' else 'Yes'

    3. Create a attribute in your date dimension for this.

    5. In Power View, use this attribute as filter and select 'Yes'

    4. Ensure that you run a process update on this dimension on a daily basis.

    Sunday, January 12, 2014 7:03 AM

All replies

  • Easiest option is to add an attribute to your "Date" dimension and use the attribute as a slicer in Power View.

    Here is the list of steps you need to follow

    1. In your DSV, add a computed column named "Last Six Months"

    2. set the expression as "If Date < 6 months then 'No' else 'Yes'

    3. Create a attribute in your date dimension for this.

    5. In Power View, use this attribute as filter and select 'Yes'

    4. Ensure that you run a process update on this dimension on a daily basis.

    Sunday, January 12, 2014 7:03 AM
  • Hi Youdbir,

    According to your description, you create a PowerView report connect to SQL Server Analysis Services (SSAS) cube, Now what you want is that filter the data to show the last 6 month data, right? As Ananda said, you can achieve it by "add an attribute to your "Date" dimension and use the attribute as a slicer in Power View". Besides, we can use a filter in the report. In a PowerView report, we can use basic filter and advanced filter to filter data which you can see on the screenshot below.

    Reference:Filtering in Power View

    If I have anything misunderstood, please point it out.

    Regards,


    Charlie Liao
    TechNet Community Support

    Wednesday, January 15, 2014 6:26 AM
    Moderator
  • Thanks Ananda. Though we are facing problem in applying multiple dynamic filters on same date dimension but still your comments are helpful.

    Charlie - You are pointing towards filters from UI which can't be dynamic for dates (like last 30 days, last 6 months). Still thanks for your kind comments.


    Best Regards,
    Youdhbir

    Blog : http://youdhbir.blogspot.in/

    Wednesday, January 15, 2014 7:55 AM