Forum Discussion

mattkarriker's avatar
mattkarriker
Helper I
9 years ago
Solved

Time Slicer issues - SSAS MD

I have an issue where the time slicer does not seem to be working for calculated measures that use PARALLELPERIOD.  I have a report filtered to the current year.  I add the time slicer to the report ...
Need Help
v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
9 years ago

mattkarriker

 

What you created is a calculated measure, which has no aggregation usage. So when you filter on child level, the calculated measure will never filter accordingly. For your requirement, you need to create a dummy measure in your source measure group. Then create a SCOPE statement to achieve it. 

 

 

 

 

See a slimilar thread on MSDN forum:

 

https://social.msdn.microsoft.com/Forums/en-US/5e4128f3-ee4f-4178-8b4a-8432b910611e/total-calculation-with-filter-for-parallel-period?forum=sqlanalysisservices

 

Regards,

  • Matt695's avatar
    Matt695
    Frequent Visitor
    8 years ago

    Hi Folks,

     

    I am facing the same issue except I'm trying to use the relative date slicer in Power BI.  I need to be able to set it up to give me the Last 1 Months (Calendar). 

     

    This thread is great and has helped me to understand why a calculated measure for parallelperiod didn't aggregate.  I can get this example to work but in my production system I have an added challenge.  I need to apply a static set in the scope.  This worked for me but the performance is terrible (usually over 7 seconds).  Without the static set the data returns sub second.

     

    Here is what I did in the scope statement:

    scope([Date].[Calendar].[Date].members);

      [Measures].[Pre Year Reseller Sales Amount] = 

    Aggregate([Static Set Name], (parallelperiod([Date].[Calendar].[Calendar Year],1,

    [Date].[Calendar].currentmember), [Measures].[Reseller Sales Amount]));

    end scope;

     

    Any thoughts as to why this is happening?  We use the same named set in a lot of calculated measures and performance is sub second.

     

    Thanks!