Running Total Calculation

[yaman123]

Hi all, 

I have seen online and theres many ways of calculating the running total or cumulative total. 

I have a column Collection Litres and a date column Sample Date. I woud like to calculate the running total YTD for collection litres. If i select sample date 01/02/2021 - 28/02/2021, the total should sum collection litres from March 2020 until Feb 2021. 

TIA

Yasir

[v-xiaotang · Accepted solution]

Hi @yaman123 

If your column Collection Litres and column Sample Date are in the same table, you may take steps below for reference.

1. create measure total_YTD

-

total_YTD =

var mmax = maxx(ALLSELECTED(Table6),Table6[Sample Date])

var mmin = EDATE(mmax,-12)

return

calculate(sum('Table6'[Collection Litres]), FILTER(all('Table6'), 'Table6'[Sample Date] >mmin && 'Table6'[Sample Date] <=mmax))

-

-

it calculates the total_YTD between 2016/6/16  - 2017/6/30

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@yaman123 , Try a measure like

New measure =
var _max = maxx(allselected('Date', 'Date'[date])
var _min = eomonth(_max,-12)
return
calculate(sum(Table[Value]), filter(all('Date'), 'Date'[date] >=Min && 'Date'[date] <=_max))

or

New measure =
var _max = maxx(allselected('Date', 'Date'[date])
var _min = eomonth(_max,-12)
return
calculate(sum(Table[Value]), filter(all('Date'), 'Date'[date] >=Min && 'Date'[date] <=_max && Date[Date] <=max(Date[Date])))

If you need trend refer: https://www.youtube.com/watch?v=lOEW-YUrAbE