Forum Discussion
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
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
- Ritaf1983Super User
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
- attempt1Frequent Visitor
Here is the pbix with dummy data: https://www.dropbox.com/scl/fi/ua4st1m2yni1trr4btypu/Fake-dashboard-v1.pbix?rlkey=waeo4wfeaompmal79cytq1ox7&st=qa2dw5el&dl=0
Thank you