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

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.

2 Replies

  • 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

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    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.