Changing the filter context for calculating cummulative values

[Ed212]

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!

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

[Ed212]

I have edited the original post, and now it includes the files.

[lbendlin]

You need to use a proper calendar table if you want to use that slicer. (Although I would say the slicer is not actually required)

Keep in mind that the Quick Measures offer a "Running Total"  pattern - you should use that as a start.

[Ed212]

Thank you @lbendlin for your help! Unfortunately this is not what I'm looking for. I have tried every combination of quickmeasures I could think of, but no success either. I have worked further with my example (date table and so on). The updated file:
ExampleReport4.pbix 
Let me explain:

The main purpose of the calculation is to create a graph, showing the evolution of the cummulated value. Lets forget the products for a while. The result from the measure should be this (in red):

It should mantain the calculation of the cummulated value even when I change the year. It would allow me to slice the year using the slicer and "continue" with the cummulated values. Visually like this (despite the values are not right):

As you can see, if we select 2024 the first week doesn't start the cummulated value from 0. It takes the subtotal from the past years (in this case, 2023).

Any idea how can I do this?

[v-linyulu-msft · Accepted solution]

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.

4.For questions about uploading data, you can try the following links:

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Solved: How to upload PBI in Community - Microsoft Fabric Community

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ed212]

@v-linyulu-msft , it is exactly what I was looking for. Thanks a lot!!