Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello everyone
Below is the dataset that I have and i would like to calculte the running total. I have see older posts requesting similar calculation but I was not able to make it work. Is someone able to provide me with the dax function.
Thanks
Solved! Go to Solution.
@fanisgeorg 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)
Hi,
Share the link from where i can download your PBI file. Please also show the expected result there.
Thanks Ashish, unfortunately I can not share the file outside my organization
Tx
Fanis
@fanisgeorg 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
@fanisgeorg 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?
@fanisgeorg 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)