Forum Discussion

yaman123's avatar
yaman123
Post Partisan
5 years ago
Solved

Running Total Calculation

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 calcula...
Need Help
Show and Tell
Tips and Tricks
  • v-xiaotang's avatar
    v-xiaotang
    5 years ago

    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's avatar
amitchandak
Super User
5 years ago

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