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!

GosiaPio2506's avatar
GosiaPio2506
Regular Visitor
1 year ago

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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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.