1

I wanna calculate the cumulative values by month/year

My dimensions period as follow:

Period Dimension

I ve tried any ways to perform this operation, but unsucessfull

with member [Measures].[Valor Acumulado] 
as Sum(PeriodsToDate([Período].[Mês].[(All)]),[Measures].[Valor Pago]
)
 with member 
 [Measures].[Valor Acumulado] as Sum(YTD([Período].[Mês].[(All)]),
 [Measures].[Valor Pago])

Or the cumulative calcule does not work or appears a Error Message

CellOrdinal 1 VALUE #Error Query (2, 37) The YTD function expects a member expression for the argument. A string or numeric expression was used

Query execution Query execution

Is it necessary to make a concatenation with Year and Month members?

Anybody to help me?

  • could you attach a screen shot of how you are expecting the result to be. – MoazRub Feb 01 '19 at 12:53
  • Sorry, i don't know how can i reply you answer. I've added another answer with your suggestions – Tonny Alves Feb 01 '19 at 15:51
  • take a look at the screen shot. Is this how the result shot be. If not share a screen shot showing how you want the result to look like. – MoazRub Feb 01 '19 at 15:53
  • you missed the Case statement and the " : " try this in your code [Measures].[Valor Acumulado] as Case when [Measures].[Valor Pago] = null then null else Sum({[Período].[Mês].firstchild : [Período].[Mês].currentmember},[Measures].[Valor Pago]) end – MoazRub Feb 01 '19 at 15:57
  • I don't have null values, but i've add the case statement with member [Measures].[Valor Acumulado] as case when [Measures].[Valor Pago] = null then null else Sum({[Período].[Mês].firstchild:[Período].[Mês].currentmember},[Measures].[Valor Pago]) end Is not cumulating :/ I don't know what is wrong – Tonny Alves Feb 01 '19 at 16:02
  • this looks good, I hope it works – MoazRub Feb 01 '19 at 16:08
  • Well, its worked when i removed "[Período].[Nome de Mês].[Nome de Mês]". But I need this field. How can i combine this 2 fields in sum? – Tonny Alves Feb 01 '19 at 16:12
  • I presume that [Período].[Nome de Mês].[Nome de Mês] is a more detailed feild than [Período].[Mês], in that case use [Período].[Nome de Mês] in the calculated member. – MoazRub Feb 01 '19 at 16:33
  • I add [Período].[Nome de Mês].[Nome de Mês] in calculate member construction and finally works. Thanks @MoazRub for help. – Tonny Alves Feb 04 '19 at 13:14
  • you are welcome – MoazRub Feb 04 '19 at 13:18

2 Answers2

1

Take a look at the sample below, it will help.

with 
member 
[Measures].[Internet Sales AmountRunningtotal]
as 
case when [Measures].[Internet Sales Amount] = null then null 
else 
sum({[Product].[Subcategory].firstchild:[Product].[Subcategory].currentmember},[Measures].[Internet Sales Amount])
end
select {[Measures].[Internet Sales Amount],
[Measures].[Internet Sales AmountRunningtotal]
} on columns,

non empty
([Date].[Calendar Year].[Calendar Year],[Date].[Calendar Quarter of Year].[Calendar Quarter of Year],
[Product].[Category].[Category],[Product].[Subcategory].[Subcategory])
on 
rows 
from 
[Adventure Works]

Result enter image description here

MoazRub
  • 2,881
  • 2
  • 10
  • 20
1

Well, i made this sugessted changes, but the result was the same as

Query Execution

with member [Measures].[Valor Acumulado] as Sum({[Período].[Mês].firstchild 
[Período].[Mês].currentmember},[Measures].[Valor Pago])
SELECT NON EMPTY { [Measures].[Valor Pago], [Measures].[Valor Acumulado]  } ON COLUMNS, 
    NON EMPTY { ([Grupo de Despesa].[Grupo de Despesa].[Grupo de Despesa].ALLMEMBERS,
                [Período].[Ano].[Ano].ALLMEMBERS * [Período].[Mês].[Mês].ALLMEMBERS, 
                [Período].[Nome de Mês].[Nome de Mês].ALLMEMBERS ) 
                }ON ROWS 
                    FROM ( 
                        SELECT ( -{ [Unidade Governamental].[Sigla Orgão].&[ALESE], 
                                    [Unidade Governamental].[Sigla Orgão].&[DPE], 
                                    [Unidade Governamental].[Sigla Orgão].&[FAI], 
                                    [Unidade Governamental].[Sigla Orgão].&[FUNDECON], 
                                    [Unidade Governamental].[Sigla Orgão].&[MPE], 
                                    [Unidade Governamental].[Sigla Orgão].&[RC], 
                                    [Unidade Governamental].[Sigla Orgão].&[TJ], 
                                    [Unidade Governamental].[Sigla Orgão].&[TCE] } ) ON COLUMNS 

                                    from [Execução Orçamentária])