CODEWITHSUNDEEP
powerbissasmdxmeasurecumulative-sum
Joe
Joe

Reputation: 47

Running Totals with Filters in MDX?

I want to start running-total from the date I choose in filter in Power BI, because all functions MDX I have tried, start running-total from the first value.

Like in the image I have shared I want running-total of SUM column start from the date i choose in filter.

My Dashbord in Power BI is connected to a OLAP Cube SSAS (Data source).

Thank you !

enter image description here

Upvotes: 1

Views: 682

Answers (1)

MoazRub
MoazRub

Reputation: 2911

The reason for this is that in your running total you are summing the values without context.

Take a look at the example 

with 
member 
[Measures].[Internet Sales AmountRunningtotal]
as 
case when [Measures].[Internet Sales Amount] = null then null 
else 
sum( {[Date].[Calendar Year].firstchild:[Date].[Calendar Year].currentmember},[Measures].[Internet Sales Amount])
end

select {[Measures].[Internet Sales Amount],
[Measures].[Internet Sales AmountRunningtotal]
} on columns,

non empty
([Date].[Calendar Year].[Calendar Year])
on 
rows 
from 
[Adventure Works]

Result enter image description here

Now lets make some changes 

with 
member 
[Measures].[Internet Sales AmountRunningtotal]
as 
case when [Measures].[Internet Sales Amount] = null then null 
else 
sum(existing {[Date].[Calendar Year].firstchild:[Date].[Calendar Year].currentmember},[Measures].[Internet Sales Amount])  
end 

select {[Measures].[Internet Sales Amount],
[Measures].[Internet Sales AmountRunningtotal]
} on columns,

non empty
([Date].[Calendar Year].[Calendar Year])
on 
rows 
from 
(select[Date].[Calendar Year].&[2012]:[Date].[Calendar Year].&[2013] on 0 from [Adventure Works] )

Result

enter image description here

Notice that in the second result the values for years before 2012 were not included. This all happens becuse of the use of "existing" keyword which ensure the expression is evaluated within context.

Upvotes: 1

Related Questions