2

I was able to calculate PY Total for Selected Time Dimension's Attributes in individual Hierarchy but cannot figure out how to make it work for both Time Dimension Hierarchies with single calculated measure.

I had to USE Dynamic SET to populate Days and use AGGREGATE on the SET as follows -

CREATE DYNAMIC SET CURRENTCUBE.[Days]                                                        
 AS {EXISTING [Time].[Time].[Day]};                 
CREATE MEMBER CURRENTCUBE.[Measures].[PY Sales Selected]                                     
 AS AGGREGATE(                                                                
        GENERATE(EXISTING [Days],     
            {PARALLELPERIOD([Time].[Time].[Year],1,[Time].[Time].CurrentMember)}
        ),
[Measures].[Sales]
),   
FORMAT_STRING = "#,#", 
VISIBLE = 1

----
CREATE DYNAMIC SET CURRENTCUBE.[Days] 
 AS {EXISTING [Time].[Current].[Curr Day Flag]};                
CREATE MEMBER CURRENTCUBE.[Measures].[PY Sales Selected]                                     
 AS AGGREGATE(                                                                
        GENERATE(EXISTING [Days],     
            {PARALLELPERIOD([Time].[Current].[Curr Year Flag],1,[Time].[Current].CurrentMember)}
        ),
[Measures].[Sales]
),   
FORMAT_STRING = "#,#", 
VISIBLE = 1

Both calculations work individually but I would like to combine them so that I could just have one Calculated Measure [Measures].[PY Sales Selected].

We have following Time dimension structure -

Time Dimension Structure

Attribute Relationship

I would appreciate any help!!

Anup Pudasaini
  • 53
  • 6
  • Is there a reason you need such a complex calculation for PY Sales Selected? Would you get the same result with something like CREATE MEMBER CURRENTCUBE.[Measures].[PY Sales Selected] AS (PARALLELPERIOD([Time].[Time].[Year]),[Measures].[Sales]))? If so I think I can suggest a solution for merging the calculations. – Brenton Oct 27 '21 at 10:36
  • Yes, but that would only work for single selection of hierarchy members (Quarters/Months). If I select multiple values, it would TOTAL for the whole YEAR not for those selected. I needed to get PY Total based upon the selected members. – Anup Pudasaini Oct 27 '21 at 13:32

0 Answers0