Calculating and displaying max of a running total

[joemuziki]

I have the following source data 

Account	Joined Date	Transaction Date	Transaction Value
Account A	08/10/2019	14/10/2019	$2600.00
Account A	08/10/2019	24/10/2019	-$650.00
Account A	08/10/2019	22/11/2019	-$650.00
Account A	08/10/2019	25/11/2019	$650.00
Account A	08/10/2019	10/12/2019	$650.00
Account A	08/10/2019	24/12/2019	-$650.00
Account B	09/10/2019	06/12/2019	$30.58
Account B	09/10/2019	18/12/2019	$214.38
Account B	09/10/2019	24/12/2019	-$244.96
...	...	...	...

I am trying to create this final result table in Power BI:

Account	Joined Date	Max Balance	Total Funds Disbursed	%
Account A	08/10/2019	$2600.00	-$1950.00	75
Account B	09/10/2019	$244.96	-$244.96	100
...	...	...	...	..

Where:

1. Max Balance = Max of running total of 'Transaction Value'
2. Total Funds Disbursed = Total of negative transaction values of an account 
3. % = ( Total Funds Disbursed / Max balance )

How best can I create this table visual in Power BI Desktop? What is the correct correct DAX to generate the 3 desired columns?

[parry2k]

@joemuziki not sure about your business logic but this what you can try

add new column:

Abs = ABS ( 'Table'[Transaction Value] )

add new measures

Max Balance = MAX ( 'Table'[Abs] )

Disbursed = CALCULATE ( SUM ( 'Table'[Transaction Value] ), 'Table'[Transaction Value] < 0 )

% = DIVIDE( ABS( [Disbursed] ), [Max Balance] )

here is the output    . Would appreciate Kudos 🙂 if my solution helped.

[az38]

hi @parry2k 

beautiful, but not sure it will work

Disbursed = CALCULATE ( SUM ( 'Table'[Transaction Value] ), 'Table'[Transaction Value] < 0 )

as it could be a lot unpredictable +-+---+--+--+ transaction, and we need max cumulative Amount on the account. So, as I see, we need a maximum sum on the account for its life time

do not hesitate to give a kudo to useful posts and mark solutions as solution

[az38 · Accepted solution]

Hi @joemuziki 

try this technique:

1. create a measure in your table

Measure = 
calculate(sum('Table1'[Transaction Value]);filter(allexcept('Table1';Table1[Account]);Table1[Transaction Date]<=SELECTEDVALUE(Table1[Transaction Date])))

2. create a calculated table

Table = 
addcolumns(
SUMMARIZE(
'Table1';Table1[Account];Table1[Joined Date ];
"Max Balance";MAXX(Table1;[Measure]);
"Total Funds Disbursed";sumx(Table1;calculate(sum(Table1[Transaction Value]);Table1[Transaction Value]<0))
);
"%";ABS(DIVIDE([Total Funds Disbursed];[Max Balance]))
)

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

[joemuziki]

Thanks @az38 this is definitely on the right track. I am unable to completely verify as the table is bringing back too many results and is breaching the Power BI limit. How can I apply a filter that limits the results of the summary table to:

1. % > 40%

2. Transactions Dates in the last 90 days

Thanks in advance

[az38]

@joemuziki
Try

Measure =
calculate(sum('Table1'[Transaction Value]);filter(allexcept('Table1';Table1[Account]);Table1[Transaction Date]<=SELECTEDVALUE(Table1[Transaction Date]) && datediff(Table1[Transaction Date];today();day) <= 90))

And

Table =
Filter(
addcolumns(
SUMMARIZE(
'Table1';Table1[Account];Table1[Joined Date ];
"Max Balance";MAXX(Table1;[Measure]);
"Total Funds Disbursed";sumx(Table1;calculate(sum(Table1[Transaction Value]);Table1[Transaction Value]<0))
);
"%";ABS(DIVIDE([Total Funds Disbursed];[Max Balance]))
);
[%]>0.4
)

do not hesitate to give a kudo to useful posts and mark solutions as solution

[joemuziki]

So this has been working like a charm @az38 , thank you very much. I am however bumping into an issue when I change the 'Transaction Date' column from a date to a date-time column in order to calculate the intraday running balance. I am getting an error that I am exceeding the 1000000 row limit. The underlying data set has not changed. Any thoughts on how to resolve this? 

[az38]

Hi @joemuziki 

see here https://docs.microsoft.com/en-us/power-bi/service-get-data 

---

Row limit - When using DirectQuery, Power BI imposes a limit on the query results that are sent to your underlying data source. If the query sent to the data source returns more than one million rows, you see an error and the query fails. Your underlying data can still contain more than one million rows. You're unlikely to run into this limit as most reports aggregate the data into smaller sets of results.

---

so, first be sure you are really get less then million rows or change your data source query

do not hesitate to give a kudo to useful posts and mark solutions as solution