topic Re: Running Sum Formula Not Working in DAX Commands and Tips

Running Sum Formula Not Working

GosiaPio2506 — Wed, 30 Apr 2025 14:10:33 GMT

Hi All,

I have an issue with my running sum formula. My result is supposed to be the sum from the past 3 months. For example, in April, I want to get the sum for the period Jan-Mar; in May, I want to get the sum for the period Feb-Apr, etc. However, for some reason, this formula is only returning the sum for the current month.

I need to add that the startDate variable is returning correct values only when the formula is excluding blanks.

Any idea why it is not working and what I should do to make it work?
Thank you,

Running Sum Test =

VAR currDate = MAX('Sales'[Date])

VAR startDate = EDATE(IF(ISBLANK(currDate), BLANK(), DATE(YEAR(currDate), MONTH(currDate), 1)), -3)

VAR endDate = EOMONTH(currDate, -1)

VAR period =

DATESBETWEEN('Sales'[Date], startDate, endDate)

VAR NetAmount =

SUM('Sales'[Net Amount])

RETURN

CALCULATE(NetAmount, FILTER('Sales', 'Sales'[Date] IN period))

Re: Running Sum Formula Not Working

johnt75 — Wed, 30 Apr 2025 15:28:20 GMT

Variables in DAX aren't really variable, they are constants, only evaluated when they are defined, so when you include the NetAmount variable in CALCULATE it isn't being recalculated.

Try

Running Sum Test = VAR currDate = MAX ( 'Sales'[Date] ) VAR startDate = EDATE ( IF ( ISBLANK ( currDate ), BLANK (), DATE ( YEAR ( currDate ), MONTH ( currDate ), 1 ) ), -3 ) VAR endDate = EOMONTH ( currDate, -1 ) VAR period = DATESBETWEEN ( 'Sales'[Date], startDate, endDate ) RETURN CALCULATE ( SUM ( 'Sales'[Net Amount] ), 'Sales'[Date] IN period )

Re: Running Sum Formula Not Working

Anonymous — Fri, 02 May 2025 07:41:24 GMT

Hi GosiaPio2506 ,

Thank you for reaching out to the Microsoft Fabric Community Forum.

I've reviewed your issue and can confirm that johnt75 guidance is correct.

The main issue with your original formula is the use of the NetAmount variable. In DAX, variables are static within their evaluation context and do not dynamically respond to changes in filter context when used inside a CALCULATE function. Consequently, defining NetAmount outside of CALCULATE prevents it from being influenced by the date filters applied within.

The corrected formula suggested by the Super User resolves this by placing SUM('Sales'[Net Amount]) directly inside the CALCULATE function. This ensures the expression is dynamically evaluated based on the date range filters, accurately reflecting the running total for the 3-month period prior to the current month.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thankyou.

Re: Running Sum Formula Not Working

GosiaPio2506 — Fri, 02 May 2025 12:02:56 GMT

Thank you Johnt75,
I do get an error on the return line.

RETURN CALCULATE ( SUM ( 'Sales'[Net Amount] ), 'Sales'[Date] IN period )

the last part which is 'Sales'[Date] IN period is giving an error and I have no value returned. Do you think it might have to do with the format of the data? In fact PowerBI doesn't even let me put 'Sales'[Date] column in the formula.
Any idea why it is acting like that?

Re: Running Sum Formula Not Working

aduguid — Sat, 03 May 2025 12:11:44 GMT

Try this DAX

Running Sum Test = VAR currDate = MAX('Sales'[Date]) VAR startDate = EDATE(DATE(YEAR(currDate), MONTH(currDate), 1), -3) VAR endDate = EOMONTH(currDate, -1) VAR period = DATESBETWEEN('Sales'[Date], startDate, endDate) RETURN CALCULATE( SUM('Sales'[Net Amount]), FILTER( ALL('Sales'[Date]), // ensures proper context removal 'Sales'[Date] IN period ) )

Re: Running Sum Formula Not Working

Anonymous — Mon, 05 May 2025 06:58:42 GMT

Hi GosiaPio2506 ,

You're correct, the issue is due to the use of 'Sales'[Date] IN period outside of a valid row context. In DAX, this needs to be wrapped within a FILTER() function to evaluate properly.

As correctly pointed out by aduguid , the formula below addresses this by introducing the necessary context handling:

This should return the correct 3-month running total as expected.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thankyou.

Re: Running Sum Formula Not Working

Anonymous — Fri, 09 May 2025 04:09:12 GMT

Hi GosiaPio2506 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

Re: Running Sum Formula Not Working

Anonymous — Mon, 12 May 2025 04:58:28 GMT

Hi GosiaPio2506 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

Re: Running Sum Formula Not Working

Anonymous — Thu, 15 May 2025 12:31:21 GMT

Hi @GosiaPio2506 ,

Thank you.

Re: Running Sum Formula Not Working

GosiaPio2506 — Thu, 15 May 2025 12:35:32 GMT

Hi,

No, we decided to go with different approach and not calculate this in Power BI. I believe that there is an issue with the data and this is why the formulas are not working.

Thank you!

Re: Running Sum Formula Not Working

Anonymous — Mon, 19 May 2025 03:55:30 GMT

Hi GosiaPio2506 ,

Thank you for the update. I understand you've decided to go with a different approach.

I appreciate the effort you've put into trying to resolve the issue. As you mentioned, it does seem likely that the data inconsistencies are affecting the formulas in Power BI.

If you happen to find a solution on your side, please feel free to share it here it would be helpful for the other people in the community.

Thank you.