Running Sum Formula Not Working

[GosiaPio2506]

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))

[v-tsaipranay]

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.

[v-tsaipranay]

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.

[GosiaPio2506 · Accepted solution]

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!

[v-tsaipranay]

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.

[aduguid]

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
        )
    )

[GosiaPio2506]

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?

[v-tsaipranay]

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:

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]),
            'Sales'[Date] IN period
        )
    )

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.

[v-tsaipranay]

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.

[v-tsaipranay]

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.

[johnt75]

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 )