Forum Discussion
Running Sum Formula Not Working
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!
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.
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.