Forum Discussion
Helper IIICumulative sum less than selected month
Hi,
I have a Table and related to Calendar table (many to one). I have a slicer from Calendar table(Month col) and Bar chart(X axis - Month col from Calendar table and Cumulative Sales from Table in a page.
Table:
| Date | Product | Sales |
| 5/22/2025 | P1 | 5 |
| 5/23/2025 | P1 | 10 |
| 6/24/2025 | P1 | 15 |
| 6/22/2025 | P2 | 10 |
| 7/23/2025 | P2 | 20 |
| 8/24/2025 | P2 | 30 |
| 1/22/2025 | P3 | 2 |
| 2/23/2025 | P3 | 4 |
| 3/24/2025 | P3 | 6 |
| 4/25/2025 | P3 | 8 |
| 4/20/2025 | P3 | 10 |
| 11/20/2025 | P4 | 5 |
| 12/20/2025 | P5 | 5 |
on report:
Requirement:
For example: If I select March 25 then result will be from Jan 25 to March 25 :
If I select Jun 25 , the result will be like from Jan 25 to Jun 25 :
means, Cumulative sales will be always <=selected month from slicer.
So that I tried, like: Cumulative Sales = CALCULATE(SUM('Table'[Sales]),ALL('Calendar'),'Calendar'[Date]<=MAX('Table'[Sales_Date]))
But Month is being filter as per slicer month selected. How can I achieve using dax.
Note: 1. Please do not create another table to use in axis because my data model have multiple table connection with multiple tables.
2. Do not disable interactions.
ot create another table
Hello Jyaul1122,
Thank you for reaching out to the Microsoft Fabric Community Forum.I've reproduced your scenario and validated your requirement to display cumulative sales from January to the selected month while keeping the full month axis visible. I initially attempted to achieve this using your existing Calendar and Sales tables, adhering to your constraint of not creating a new table. However, despite testing various DAX measures (e.g: using ALLSELECTED and proper relationships), the slicer consistently filtered out unselected months, showing only the selected month's data or incorrect totals due to Power BI's pre-filtering behavior.
When a slicer filters the same column used in a visual’s axis (e.g: Calendar[Month]), Power BI removes unselected rows from the data context before any measure or visual filter is applied. This occurs even with ALL() or ALLSELECTED() in DAX, as the slicer’s effect precedes measure evaluation. Attempts to use a Show Month Filter = 1 measure as a visual filter also didn’t preserve the full axis.
To meet your requirement, I used a disconnected Month Selector table. This approach avoids the slicer’s default filtering behavior and ensures the full month axis remains visible and For your reference, I've attached a sample .pbix file.
I recognize your preference to avoid new tables due to your complex data model. However, the intrinsic behavior of Power BI slicers with the existing setup made it impossible to preserve the full month axis without this workaround. The disconnected table ensures the slicer selection only influences the measure logic, leaving the axis intact.
Thanks for your understanding.
Best Regards,
Ganesh Singamshetty.
8 Replies
- Greg_Deckler
Community Champion
Jyaul1122 Sounds like you need this: Inverse Aggregator - Microsoft Fabric Community
v-ssriganeshCommunity Support
Hello Jyaul1122,
Thank you for reaching out to the Microsoft Fabric Community Forum.I've reproduced your scenario and validated your requirement to display cumulative sales from January to the selected month while keeping the full month axis visible. I initially attempted to achieve this using your existing Calendar and Sales tables, adhering to your constraint of not creating a new table. However, despite testing various DAX measures (e.g: using ALLSELECTED and proper relationships), the slicer consistently filtered out unselected months, showing only the selected month's data or incorrect totals due to Power BI's pre-filtering behavior.
When a slicer filters the same column used in a visual’s axis (e.g: Calendar[Month]), Power BI removes unselected rows from the data context before any measure or visual filter is applied. This occurs even with ALL() or ALLSELECTED() in DAX, as the slicer’s effect precedes measure evaluation. Attempts to use a Show Month Filter = 1 measure as a visual filter also didn’t preserve the full axis.
To meet your requirement, I used a disconnected Month Selector table. This approach avoids the slicer’s default filtering behavior and ensures the full month axis remains visible and For your reference, I've attached a sample .pbix file.
I recognize your preference to avoid new tables due to your complex data model. However, the intrinsic behavior of Power BI slicers with the existing setup made it impossible to preserve the full month axis without this workaround. The disconnected table ensures the slicer selection only influences the measure logic, leaving the axis intact.
Thanks for your understanding.
Best Regards,
Ganesh Singamshetty.- DataNinja777
Super User
Hi Jyaul1122 ,
You can solve this by creating two separate DAX measures. One measure will handle the cumulative sales calculation, and the second will be used as a special filter on your bar chart visual. This method correctly displays all months up to your selection without needing extra tables or disabling the slicer's interactions.
First, create the measure to calculate the running total of sales. This formula calculates the sum of sales for all dates up to the maximum date in the current context of the visual (i.e., the end of each month shown on the axis).
Cumulative Sales = CALCULATE( SUM('Table'[Sales]), FILTER( ALL('Calendar'), 'Calendar'[Date] <= MAX('Calendar'[Date]) ) )Next, create the measure that will control which months appear on the chart. This is the key to the solution. It identifies the last date of the month you select in the slicer and then returns a value of 1 only for months on the axis that are earlier than or equal to your selection.
Show Month Filter = VAR SelectedMonthEndDate = CALCULATE(MAX('Calendar'[Date]), ALLSELECTED('Calendar')) VAR CurrentMonthEndDate = MAX('Calendar'[Date]) RETURN IF(CurrentMonthEndDate <= SelectedMonthEndDate, 1, BLANK())To implement this, add both measures to your model. Build your bar chart by placing the Calendar[Month] column on the X-axis and your new Cumulative Sales measure on the Y-axis. The final step is to select the chart, drag the Show Month Filter measure to the "Filters on this visual" pane, and set its filter condition to "is 1". This tells the chart to only show the bars for which the Show Month Filter measure returns a 1.
This works because your initial problem was that the slicer directly filtered the chart's axis, leaving only the single selected month visible. The Show Month Filter measure bypasses this by creating a new rule for visibility. It forces the chart to render all months up to your selection, allowing the Cumulative Sales measure to correctly calculate the running total across those months.
Best regards,
Jyaul1122Hi, Thanks for your reply, I tired the same way but Month axis is filtered based on slicer selection.
,
If is it done from your end, could you please share PBIX ?
- v-ssriganesh
Hello Jyaul1122,
Hope everything’s going great on your end. Just checking in has the issue been resolved or are you still running into problems? Sharing an update can really help others facing the same thing.
Thank you.
- v-ssriganesh
Hello Jyaul1122,
We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.
Thank you.
- v-ssriganesh
Hello Jyaul1122,
Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.
Thank you for being part of the Microsoft Fabric Community.
