Filtered Accumulation in DAX

[User1245]

Hello,

I'm trying to create a cumulative chart where the X-axis displays every week of the year, and the Y-axis shows cumulative results. For example, if I have 2 results in week 01/2023, I want that value to persist in the following weeks until a new result appears. So if the next result is 1, the chart should show 3 from that point onward, and so on. I managed to achieve this behavior using DAX, but the measure doesn't work correctly when slicers are applied. The results are correct, but it fails to show the "empty" weeks 
(Image1: no filter, Image2: filtered)

Mind you both sheets are connected by the column Dates, which in common have the first day of each week of the year. How can I fix this?

Here is my measure code: 

Test =

VAR ActualWeek = SELECTEDVALUE(Calendar[Week/Year])

VAR SelectedYear = SELECTEDVALUE(Calendar[Year])

VAR Firstweekwithdata =

    CALCULATE(

        MIN(Calendario[Week/Year]),

        FILTER(

            ALLSELECTED(Calendar),

            Calendar[Year] = SelectedYear &&

            CALCULATE(

                DISTINCTCOUNT('KPI'[PART. NUMBER]),

                'KPI'[Data] = "Realizado"

            ) > 0

        )

    )

RETURN

    IF(

        ActualWeek >= Firstweekwithdata,

        CALCULATE(

            SUMX(

                FILTER(

                    ALLSELECTED(Calendar),

                    Calendar[Week/year] <= ActualWeek &&

                    Calendar[Week/year] >= Firstweekwithdata &&

                    Calendar[Week/year]  = SelectedYear

                ),

                CALCULATE(

                    DISTINCTCOUNT('KPI'[PART. NUMBER]),

                    'KPI'[Data] = "Realizado"

                )

            )

        ),

        0

    )

[User1245 · Accepted solution]

Hey guys! Here's my data model. All columns are text, except "Datas": 

"Semana/Ano" means "Week/Year". "Datas" mean "Dates". Both reports are connected by "Dates". I used your tips and got two different results: 

The "Year" slicer functions correctly on both charts.
However, the other slicers only work on the top chart, which displays non-cumulative data. In both charts, the bars represent actual results, while the line indicates the target. 

My database originally had each "Event" as separate columns with corresponding dates. I’ve unpivoted the data into Events and Datas.

[v-menakakota]

Hi @User1245 ,

Thanks for reaching out to the Microsoft fabric community forum. 

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.
Please show the expected outcome based on the sample data you provided.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

 

Best Regards, 
Community Support Team  

[v-menakakota]

Hi @User1245 ,

We’re following up to check whether you were able to look into our earlier discussion. If you're still facing challenges, sharing a sample dataset will enable us to provide a focused and effective solution.

We’re here to help if you have any additional questions.

Thank you.

[v-menakakota]

Hi @User1245 ,

We’re following up to check whether you were able to look into our earlier discussion. If you're still facing challenges, sharing a sample dataset will enable us to provide a focused and effective solution.

We’re here to help if you have any additional questions.

Thank you.

[v-menakakota]

Hi @User1245 ,

Can you please confirm whether you have resolved issue. If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. This will be helpful for other community members who have similar problems to solve it faster. 

If we don’t hear back, we’ll go ahead and close this thread.Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

Thank you.

[MarkLaf]

Exactly what you want is not clear to me without sample start-state and end-state data. I'll go through a quick example using my own test data, trying to reflect what I can glean are your column names. If the below isn't responsive, please try providing some sample data for us to work off.

Tables:

KPI

Date	Data	PART. NUMBER
11/10/2024	Realizado	1
11/24/2024	--	1
12/8/2024	Realizado	2
12/22/2024	Realizado	2
1/19/2025	Realizado	3
1/26/2025	Realizado	3
2/23/2025	Realizado	3
3/2/2025	--	3
3/9/2025	Realizado	4
3/16/2025	--	5
5/4/2025	Realizado	6
6/22/2025	--	6
6/29/2025	Realizado	6
7/13/2025	Realizado	7

Calendar (✓ Mark as date table)

Calendar = 
GENERATE(
    CALENDARAUTO(),
    ROW(
        "Year", YEAR( [Date] ),
        "WeekdayNum", WEEKDAY( [Date], 1 )
    )
)

(snip)

Model:

Visual + Measure:

Assuming that the same PART.NUMBER should only count as 1 across weeks, we can use the following measure.

Part Distinct Count YTD = 
CALCULATE( 
    DISTINCTCOUNT( 'KPI'[PART. NUMBER] ) 
        + IF( NOT ISEMPTY( 'Calendar' ), 0 ), 
    'KPI'[Data] = "Realizado", 
    CALCULATETABLE( 
        DATESYTD( 'Calendar'[Date] ), 
        'Calendar'[WeekdayNum] = 1 
    )
)

Or, if PART.NUMBER repeated in a new week should count as a +1 (ie it's the unique count of parts within each week):

Part Weekly Distinct Count YTD = 
CALCULATE( 
    SUMX( 
        VALUES( 'Calendar'[Date] ), 
        CALCULATE( DISTINCTCOUNT( 'KPI'[PART. NUMBER] ) ) 
    ) + IF( NOT ISEMPTY( 'Calendar' ), 0 ), 
    'KPI'[Data] = "Realizado", 
    CALCULATETABLE( 
        DATESYTD( 'Calendar'[Date] ), 
        'Calendar'[WeekdayNum] = 1 
    ) 
)

Use Calendar fields for the axis (and it appears you want categorical, not continuous, on the x-axis). Here is snip with the first measure.

[User1245]

I just posted my data. Your insight helped me make the Year slicer work, but I still face the same problem with the other slicers 😕

[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.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[maruthisp]

Hi User1245,

As per the original post, I tried to come up with a solution. Please check the below attached pbix file.

Filtered Accumulation in DAX.pbix

Please let me know if you have any further questions or need clarifications.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

[lbendlin]

To report on things that are not there you need to use disconnected tables and/or crossjoins

In your case you need a disconnected calendar table and measures.

[Elena_Kalina]

Hi @User1245 

Try this one

Test = 
VAR ActualWeek = SELECTEDVALUE(Calendar[Week/Year])
VAR SelectedYear = SELECTEDVALUE(Calendar[Year])
VAR FirstWeekWithData =
    CALCULATE(
        MIN(Calendar[Week/Year]),
        FILTER(
            ALL(Calendar),
            Calendar[Year] = SelectedYear &&
            NOT(ISBLANK(
                CALCULATE(
                    DISTINCTCOUNT('KPI'[PART. NUMBER]),
                    'KPI'[Data] = "Realizado"
                )
            ))
        )
    )
VAR CumulativeValue =
    CALCULATE(
        DISTINCTCOUNT('KPI'[PART. NUMBER]),
        'KPI'[Data] = "Realizado",
        FILTER(
            ALL(Calendar[Week/Year], Calendar[Year]),
            Calendar[Year] = SelectedYear &&
            Calendar[Week/Year] <= ActualWeek
        )
    )
RETURN
    IF(
        ActualWeek >= FirstWeekWithData && NOT(ISBLANK(ActualWeek)),
        CumulativeValue,
        0
    )

If this doesn’t help, could you share an example report (while avoiding confidential data, of course) to better understand your issue?

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[User1245]

Hi, thanks for your reply!

It almost worked. When I use a slicer, the weeks are no longer hidden — which is great. However, the values are no longer accumulating over time. 

[Elena_Kalina]

Сould you share an example report (while avoiding confidential data, of course) to better understand your issue?