Running Total on more than one field

[steveorme69]

1st post and only been using Power BI for 2 weeks !

I'm trying to build a cashflow report to show running balances across multiple bank accounts/nominal codes, I used the quick measure function but it only gave me the option to use one field for the running total. I need to calculate on the date and the nominal, by using the quick set-up I think it is adding up across all nominal codes with the same dates.

Net Balance running total in Date  =
CALCULATE(
 SUM('Sage Trans Raw Data'[Net Balance]),
 FILTER(
  ALLSELECTED('Sage Trans Raw Data'[Date]),
  ISONORAFTER('Sage Trans Raw Data'[Date], MAX('Sage Trans Raw Data'[Date]), DESC)
 )
)

Thanks

Steve

[Ashish_Mathur]

Hi,

Share the dataset and show the expected result.

[Anonymous]

Assuming this formula already works, have you tried adding more files into the row containing the sum?  Something like:

Net Balance running total in Date  =
CALCULATE(
 SUM('Sage Trans Raw Data'[Net Balance]) + SUM('Sage Trans Raw Data'[SomeOtherField])
 FILTER(
  ALLSELECTED('Sage Trans Raw Data'[Date]),
  ISONORAFTER('Sage Trans Raw Data'[Date], MAX('Sage Trans Raw Data'[Date]), DESC)
 )
)

This would total both the Net Balance and 'SomeOtherField' into a single figure, which is what it sounds like you want?

[steveorme69]

Hi appreciate your response. 

I tried your suggestion but It now shows a syntax error on the filter. 

I need the running total on a number of different nominals assigned to each bank account each could have different dates for transactions. Would the code happen in the filter rather than the sum as I want to sum on each change of nominal and date?

thanks Steve 

[Anonymous]

The calculate function is pretty much "Run the first parameter" with the additional filters set in the 2+ parametres.  Most of the time you don't need the Filter function within Calculate, as it already does this.

So if I was to optmise your original formula, again assumign it already worked, it should really look like:

Net Balance running total in Date  =
CALCULATE(
    SUM('Sage Trans Raw Data'[Net Balance]) + SUM('Sage Trans Raw Data'[SomeOtherField]),
    ALLSELECTED('Sage Trans Raw Data'[Date]),
    ISONORAFTER('Sage Trans Raw Data'[Date], MAX('Sage Trans Raw Data'[Date]), DESC),
)

Having edited that formula, i spotted the syntax error.  When i created the formula last time, i missed out a comma between the Sums you want to add up and the filter section.

[steveorme69]

Many thanks but the [someotherfield] is a text so it doesn't like that.

What's the best way to share an example of the data ?

Thanks

Steve

[steveorme69]

https://1drv.ms/f/s!An_LprApKikkbLUKLGWVylqWcMI

[Ashish_Mathur]

Hi,

I have downloaded the Excel file but your question is not clear.  Please clearly show the expected result.

[steveorme69]

Hi,

I need column G to hold the cummulative balance in changes on column A (nominal code) and column E the date.

Thanks

Steve

[Ashish_Mathur]

Hi,

You may refer to my solution here.

Hope this helps.

[steveorme69]

Hi Ashish,

Thank you much appreciated, it has worked at nominal and date level but unfortunately when I group up a level (Nom_sub_level_2) it loses the running total.

Regards

Steve

[Ashish_Mathur]

Hi,

I am not clear.  Please show a screenshot of the problem you are facing.

[steveorme69]

Hi Ashish

https://1drv.ms/f/s!An_LprApKikkbLUKLGWVylqWcMI

[Ashish_Mathur]

Which file from there do i have to download.  Describe the question clearly and also show the expected result.

[steveorme69]

It's the cash balances running total and I'd expect to see a total across for all bank accounts in the last column

Thanks

Steve

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[steveorme69]

Hi Ashish,

Sorry but how do I share the power BI output ?

Thanks

Steve

[Anonymous]

If you are running a reasonable newish version of windows you can make use of the Snipping Tool to take easy screenshots. You can access this from the Run menu (Windows Key + R) and type in SnippingTool.  From here, save the picture to your computer and add it to your reply using the "Photos" button

[steveorme69]

Thanks Ross..........that was so easy