Forum Discussion

GosiaPio2506's avatar
GosiaPio2506
Regular Visitor
1 year ago
Solved

Running Sum Formula Not Working

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 t...
  • GosiaPio2506's avatar
    GosiaPio2506
    1 year ago

    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!

johnt75's avatar
johnt75
Super User
1 year ago

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 )