Issue with Cumulative Sum Measure

[attempt1]

Hello,

My measure to calculate the cumulative sum of rows (excluding rows where the 'End' column date has past) is not working as intended in my stacked column chart when no slicer filter is selected.

The y-axis of my stacked column chart uses this measure:

all_data_cumulative =
CALCULATE(
COUNTROWS('all_data'),
FILTER(
ALLSELECTED('all_data'),
'all_data'[Start] <= MAX('date_table'[Date]) &&
'all_data'[End] > MAX('date_table'[Date]) &&
('all_data'[Type] = SELECTEDVALUE('all_data'[Type], 'all_data'[Type]))
)
)

The x-axis of the chart is a 'Date' column from the 'date_table' (the date table), while the Legend is based on the 'Type' column from the 'all_data' table. My dashboard also incorporates a slicer with the 'Type' field (from 'all_data' table) with the options 'Cars' and 'Trucks'.

Everything works perfectly when either 'Cars' or 'Trucks' is selected in the slicer. (Screenshots below)

However when neither 'Type' is selected, both types y-axis values are added together for each 'Type' which is not intended after no 'Start' dates occur (2025 in this case) screenshot below

My goal is for each 'Type' bar to reflect the all_data_cumulative measure solely for that type. Similar to below:

Also, a relationship exists between the 'Start' column of the 'all_data' table and the 'Date' column of the 'date_table' with a "many-to-one" relationship defined. Also, the columns 'Start', 'End', and 'Date' are all of Date type.
Thank you

[attempt1]

Here is the pbix with dummy data: https://www.dropbox.com/scl/fi/ua4st1m2yni1trr4btypu/Fake-dashboard-v1.pbix?rlkey=waeo4wfeaompmal79c...

Thank you

[hnguy71 · Accepted solution]

Hi @attempt1 ,

try this:

all_data_cumulative = 

VAR _CurrDate = MAX('date_table'[Date])

RETURN

CALCULATE(
    COUNTROWS('all_data'),
    KEEPFILTERS(
        'all_data'[Start] <= _CurrDate &&
        'all_data'[End] > _CurrDate
    ),
    ALLSELECTED(date_table)
)

This may be your expected output:

[attempt1]

Yes, thank you so much @hnguy71 , exactly what I wanted.

[Ritaf1983]

Hi @attempt1 

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly