Search code examples
powerbissasmdxmeasurecumulative-sum

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

Solution

  • 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.