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)
Hi, it has to do with the filter context not applying the way you think it will. When you use the slicer on one table it does not force that relationship backwards against the arrows. There are may ways to handle it. The simlest is to edit the relationship so it crossfilters in both directions. This however is not the most robust solution. Take a look at this tutorial I put together for an internal user group for some more robust solutions that will work as you develop more complicated models.
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.]))
)