Forum Discussion

attempt1's avatar
attempt1
Frequent Visitor
1 year ago
Solved

Issue with Cumulative Sum Measure

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

  • hnguy71's avatar
    hnguy71
    1 year ago

    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:

     

     

4 Replies