topic Re: Cumulative sum less than selected month in DAX Commands and Tips

Cumulative sum less than selected month

Jyaul1122 — Wed, 23 Jul 2025 14:28:22 GMT

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

Re: Cumulative sum less than selected month

DataNinja777 — Wed, 23 Jul 2025 14:57:37 GMT

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,

Re: Cumulative sum less than selected month

Greg_Deckler — Wed, 23 Jul 2025 15:03:11 GMT

Jyaul1122 Sounds like you need this: Inverse Aggregator - Microsoft Fabric Community

Re: Cumulative sum less than selected month

Jyaul1122 — Thu, 24 Jul 2025 07:15:44 GMT

DataNinja777

Hi, 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 ?

Re: Cumulative sum less than selected month

v-ssriganesh — Fri, 25 Jul 2025 09:54:50 GMT

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.

Re: Cumulative sum less than selected month

v-ssriganesh — Mon, 28 Jul 2025 15:29:36 GMT

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.

Re: Cumulative sum less than selected month

v-ssriganesh — Thu, 31 Jul 2025 12:00:34 GMT

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.

Re: Cumulative sum less than selected month

v-ssriganesh — Sun, 03 Aug 2025 16:41:21 GMT

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.

Re: Cumulative sum less than selected month

AlanP514 — Sun, 03 Aug 2025 17:00:17 GMT

Cumulative Sales :=
CALCULATE(
SUM('Table'[Sales]),
FILTER(
ALLSELECTED('Calendar'),
'Calendar'[Date] <= MAX('Calendar'[Date])
)
)

Hi Jyaul1122
please try this