Dynamically measured measure (MDX)

Question

good night!

I'm new to the MDX world and I'm having a hard time creating a calculated measure, the problem is this: I have a measure that is calculated using the final balance of the previous month as the balance of the current month. Something similar to what they see below.

This is how the data should appear The problem is that the mdx script I did not add the previous data to the display and so it ends up showing me an incorrect initial value. In the image above it should return the final balance calculated from the beginning of the company to the selected filter. Can anyone help me?

This is how it is showing up. Here is the mdx query I created:

with Member [Saldo Final 2] as ([Measures].[Certificados Novos] + [Measures].[Reativados] - [Measures].[Cancelados Menor ou Igual a 6 Parcela] - [Measures].[Cancelados Maior que a 6 Parcela]) member [Saldo Final Mês Anterior 2] as (ParallelPeriod ([DIM TEMPO].[MES ANO].[MES ANO] , 1 , [DIM TEMPO].[MES ANO].CurrentMember ), [Saldo Final 2]) member [Saldo Inicial 2] as Sum([DIM TEMPO].[MES ANO].CurrentMember.FirstSibling:[DIM TEMPO].[MES ANO].CurrentMember, [Saldo Final Mês Anterior 2]) member [Final] as [Saldo Inicial] + [Measures].[Saldo Final 2]

select [DIM TEMPO].[MES ANO].[MES ANO] on columns, {[Measures].[Certificados Novos], [Measures].[Reativados], [Measures].[Cancelados Menor ou Igual a 6 Parcela], [Measures].[Cancelados Maior que a 6 Parcela], [Final] } on rows from [dw_sinaf] where {[DIM TEMPO].[ANO].&[2016]}

Alex Peshik Alex Peshik · Accepted Answer · 2018-06-22T12:54:58

Not sure it's exactly what are you looking for, but I was able to do it on a simplified model:

Two measures are available:

Sales Only Units
Return Only Units

We need to have a SUM for every Day (Month in your example) running month-by-month from the initial Date.

Code for FINAL and INICIAL is here:

with member [Final] as
([Date].[Year Qtr Month].[Date].&[20170501]
,[Measures].[Sales Only Units])
+
SUM([Date].[Year Qtr Month].[Date].&[20170501]
:[Date].[Year Qtr Month].CurrentMember
,[Measures].[Return Only Units])

member [Inicial] as
([Final]-[Measures].[Return Only Units])

select non empty [Date].[YQM].[Date].members on 0
,{[Inicial],[Measures].[Sales Only Units],[Measures].[Return Only Units],[Final]} on 1
from [My_Cube]

BLUE is initial value "from the beginning of the company"

RED values to use if required to use another initial date

GREEN required running value

[Sales Only Units] should be ideally hidden for every member, except initial date in order not to confuse business users.

Dynamically measured measure (MDX)

1 Answers