Forum Discussion
Running Total
Anonymous I am thinking something like below. 'Dates'[Date] would reference your date or calendar table which I am assuming is what you are using for your axis.
Spent Running Total = VAR __MonthSeq = MAX('Monthly By Project Size'[Month Seq]) RETURN CALCULATE(SUM('Monthly By Project Size'[Total Spent in USD]),FILTER(ALL('Monthly By Project Size'),'Monthly By Project Size'[Month Seq] <= __MonthSeq) && 'Dates'[Date] < __MonthSeq)
Anonymous So that should be something along the lines of:
Spent Running Total Measure =
VAR __MonthSeq = MAX('Table'[Month Seq]
RETURN
SUMX(FILTER(ALL('Table'),[Month Seq] <= __MonthSeq),[Total Spent in USD])
or:
Spent Running Total Measure =
VAR __MonthSeq = MAX('Table'[Month Seq]
RETURN
CALCULATE(SUM([Total Spent in USD]),FILTER(ALL('Table'),[Month Seq] <= __MonthSeq))
Thanks Creg
In both formulas i am getting the error that the RETURN is incorrect
The syntax for 'RETURN' is incorrect. (DAX(VAR __MonthSeq = MAX('Monthly By Project Size'[Month Seq]RETURN SUMX(FILTER(ALL('Monthly By Project Size'),'Monthly By Project Size'[Month Seq] <= __MonthSeq) ,'Monthly By Project Size'[Spent
Anonymous Missing paren:
Spent Running Total Measure = VAR __MonthSeq = MAX('Table'[Month Seq]) RETURN SUMX(FILTER(ALL('Table'),[Month Seq] <= __MonthSeq),[Total Spent in USD]) or: Spent Running Total Measure = VAR __MonthSeq = MAX('Table'[Month Seq]) RETURN CALCULATE(SUM([Total Spent in USD]),FILTER(ALL('Table'),[Month Seq] <= __MonthSeq))Brilliant thanks a lot,
One last touch if you dont mind please. Can we some how show nopthing for the future months? The first line chart i have used your dax in the second chart I have done the same in my excel source, can we make the chart using the dax look like the one from excel?
Spent Running Total =VAR __MonthSeq = MAX('Monthly By Project Size'[Month Seq])RETURNCALCULATE(SUM('Monthly By Project Size'[Total Spent in USD]),FILTER(ALL('Monthly By Project Size'),'Monthly By Project Size'[Month Seq] <= __MonthSeq))