Forum Discussion

User1245's avatar
User1245
Regular Visitor
1 year ago
Solved

Filtered Accumulation in DAX

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 ...
Need Help
  • User1245's avatar
    User1245
    1 year ago

    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.

Elena_Kalina's avatar
Elena_Kalina
Solution Sage
1 year ago

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's avatar
User1245
Regular Visitor
1 year ago

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's avatar
    Elena_Kalina
    Solution Sage
    1 year ago

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