Forum Discussion
Visual Filter for Cumulative Sum Issue
AnonymousUpdate cummulative total DAX as give below and that will do it, as Seward12533 suggested to use cross filter to both direction, it has performance hit but instead of setting up the relationship, you can use it in DAX formula basically you are using cross filter to both direction when required
Cumulative = CALCULATE( SUM('Table2'[Count]), CROSSFILTER(Table1[Week of Year],Table2[Week No.], Both), FILTER(ALL('Table2'), 'Table2'[Week No.]<=MAX('Table2'[Week No.])) )Anonymous try this
Cumulative = VAR Result = CALCULATE( SUM('Table2'[Count]), CROSSFILTER(Table1[Week of Year],Table2[Week No.], Both), FILTER(ALL('Table2'), 'Table2'[Week No.]<=MAX('Table2'[Week No.])) ) RETURN IF(Result,Result,0)
AnonymousUpdate cummulative total DAX as give below and that will do it, as Seward12533 suggested to use cross filter to both direction, it has performance hit but instead of setting up the relationship, you can use it in DAX formula basically you are using cross filter to both direction when required
Cumulative =
CALCULATE(
SUM('Table2'[Count]),
CROSSFILTER(Table1[Week of Year],Table2[Week No.], Both),
FILTER(ALL('Table2'),
'Table2'[Week No.]<=MAX('Table2'[Week No.]))
)
Hi v-piga-msft just to clarify I said the cross filter was the easiest. But my attachment compared several options and recommended forcing the context via DAX or if possible slice off a bridge table that forced a standard relationship down into each of the tables. One question I have is the performance differecne of using CROSSFILTER vsreferencing the other table name (which I assume implies a cross filter) This is the method I currently favor but if there is a performance benefit to using crossfilter vs just the table name I will start using that.