Forum Discussion

nimdy's avatar
nimdy
Frequent Visitor
8 years ago

Running Total and Metric

I have a data source which contains bank account transactions. These are both positive and negative figures and can have multiple lines for a date (i.e. money paid in and bills to pay). I want to be able to display a graph which shows the bank account total (not currently in the data set) for each date, and allow the report user to use a date slicer to show the the bank balance for a certain date range e.g. 1st to 31st January.

 

In addition to this I want to show a card visual that shows the bank balance for the latest date in the date range, e.g. what was the balance on 31st Jan (or the last date in the data). This should also be controlled by the one date slicer. 

 

How can I do this?

 

 

3 Replies

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    Hi nimdy,

     

    For your requirement, you can create a index column from 1 in Query Editor: 

     

     

    Then you can create a measure below: 

     

    Running total = 
var m=LOOKUPVALUE(Sheet1[Value],Sheet1[Date],CALCULATE(MIN('Sheet1'[Date]),ALLSELECTED(Sheet1)))
var mindate=CALCULATE(MIN('Sheet1'[Date]),ALLSELECTED(Sheet1))
return IF(MAX('Sheet1'[Date])= mindate,SUM(Sheet1[Value]),CALCULATE(SUM(Sheet1[Value]),FILTER(ALLSELECTED(Sheet1),'Sheet1'[Index]<MAX(Sheet1[Index])))+m)

     

    For more information, see attached pbix file. 

     

    Best Regards,
    QQiuyunYu

    • nimdy's avatar
      nimdy
      Frequent Visitor
      8 years ago

      Thank you v-qiuyu-msft. I can see this works in your example file. It doesn't work too well for my data. I should have said my data set has nearly 700k rows. I think the data is too much for the calculations to handle as I run out of memory when trying to use the measure. Is there another way I can achieve a running total with this much data? 

      • v-qiuyu-msft's avatar
        v-qiuyu-msft
        Icon for Community Support rankCommunity Support
        8 years ago

        Hi nimdy,

         

        As you are using the slicer to dynamically return running total values, measures is good for your requirement.

         

        In your scenario, please run Power BI desktop 64 bit version instead of 32 bit. Please remove unnecessary data rows or split to several tables if possible. 

         

        Best Regards,
        Qiuyun Yu