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.

MarkLaf's avatar
MarkLaf
Super User
1 year ago

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.