Changing the filter context for calculating cummulative values

Hi there,

I'm not an expert of PowerBI, far from it. But I've been researching a lot how can I solve this problem (learning about row and filter context in measures, etc.), and I'm start becoming desperate. Surely it has a very simple solution I can't see, so a little help would be more than welcome.

Edit: This are the files I'm using:
ExampleDataset.csv and ExampleReport.pbix

This is an example table I'm using:

Where the Date column is a calculated column based on YearNumber and WeekNumber, in order to facilitate the time calculations. So I calculate the first day of every week and every year. CalculatedIndex is the sum of YearNumber*100+WeekNumber.

I want to represent visually only one year at a time, and be able to select the products in it. So I've created two slicer tables and related the tables. So far so good.

What I'm not able to calculate are the cumulated values of that sellings mantained in time. When I choose to see the 2023 values, they should be accumulated. And when I choose 2024 the values should contain the 2023 too! And of course still be able to select the products I want to see in the graph.

If I select "Chair", the result of the values should be the represented in red

Even when I select 2024, the result should be this:

As far as I know, my best shot is to use a measure and manipulate the context for the calculation (and include the past years in the calculation even if they are filtered). I've tried is this measure:

Cummulated =
VAR _context =
    ALLEXCEPT ( ExampleDataset, ExampleDataset[Product] )
VAR maxIndex =
    CALCULATE ( MAX ( ExampleDataset[CalculatedIndex] ), _context )
VAR result =
    CALCULATE (
        SUM ( ExampleDataset[Quantity Sold] ),
        FILTER ( ExampleDataset, ExampleDataset[CalculatedIndex] <= maxIndex )
    )
RETURN
    result

But it doesn't work at all, and I can't get it working. This is the result:

Anyone who could help me? Thank you in advance!

Need Help

Anonymous

1 year ago

Thanks for the reply from lbendlin , please allow me to provide another insight:

Hi, Ed212

According to our security protocols, I am currently unable to download the latest PBIX sample data you provided. However, I have obtained the sample data provided by lbendlin .

Regarding the issue you raised, my solution is as follows:

1.Considering your need to use a slicer, I created the following calculated column to perform cumulative addition:

run1 = 
VAR cy = 'example'[YearNumber]
VAR cw = 'example'[WeekNumber]
VAR aa2 =
    CALCULATE (
        SUM ( 'example'[Quantity Sold] ),
        FILTER (
            ALLSELECTED ( 'example' ),
            'example'[YearNumber] = cy - 1
                && 'example'[Product] = EARLIER ( 'example'[Product] )
        )
    )
VAR aa3 =
    IF (
        'example'[WeekNumber] = MIN ( 'example'[WeekNumber] ),
        'example'[Quantity Sold] + aa2,
        'example'[Quantity Sold]
    )
RETURN
    aa3

run2 = 
CALCULATE (
    SUM ( 'example'[run1] ),
    FILTER (
        ALLSELECTED ( 'example' ),
        'example'[Product] = EARLIER ( 'example'[Product] )
            && 'example'[YearNumber] = EARLIER ( example[YearNumber] )
            && 'example'[WeekNumber] <= EARLIER ( example[WeekNumber] )
    )
)

2.Next, I used the following measure to control the display content:

MEASURE = 
IF (
    ISINSCOPE ( 'example'[YearNumber] ),
    IF (
        ISINSCOPE ( example[WeekNumber] ),
        SUM ( 'example'[run2] ),
        SUM ( 'example'[run1] )
    ),
    CALCULATE (
        SUM ( example[run1] ),
        FILTER (
            ALLSELECTED ( 'example' ),
            'example'[YearNumber] = MAX ( 'example'[YearNumber] )
        )
    )
)

3.Here's my final result, which I hope meets your requirements.