Running Total

[fanisgeorg]

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

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.  Please also show the expected result there.

[fanisgeorg]

@Ashish_Mathur 

Thanks Ashish, unfortunately I can not share the file outside my organization

Tx

Fanis

[Greg_Deckler]

@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))

[fanisgeorg]

@Greg_Deckler 

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

[Greg_Deckler]

@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))

[fanisgeorg]

@Greg_Deckler 

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])

RETURN

CALCULATE(SUM('Monthly By Project Size'[Total Spent in USD]),FILTER(ALL('Monthly By Project Size'),'Monthly By Project Size'[Month Seq] <= __MonthSeq))

[Greg_Deckler · Accepted 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)

[fanisgeorg]

@Greg_Deckler 

Thanks, i figfured it out. thanks a lot.