topic Re: Show declining account balances in DAX Commands and Tips

Show declining account balances

KW123 — Fri, 02 Dec 2022 22:12:34 GMT

Hi,

I have data which shows a list of customer ID's and their account balances for each month. I need to build a report which will pull only the customer with declining balances over the last rolling 12 months. I am not sure how to calculate if a customer is having a declining balance. We would like to start with anyone with a 25% decrease from 12 months ago to the current date. I have a date table.

Thank you!

Re: Show declining account balances

lbendlin — Sat, 03 Dec 2022 17:41:18 GMT

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Show declining account balances

KW123 — Mon, 05 Dec 2022 05:01:53 GMT

lbendlin

Here is an example of what the data looks like

Date	Customer ID	Customer Balance
11/30/2022	123	$535
11/30/2022	234	$1045
11/30/2022	345	$324
10/31/2022	123	$525
10/31/2022	234	$1025
10/31/2022	345	$300
09/30/2022	123	$948
09/30/2022	234	$1000
09/30/2022	345	$489

It tracks each customer ID account balances until the end of time. I need to write a dax which will show only the customer ID who have had a 25% decrease in the balance from 12 months ago (a rolling 12 months) to current date.

I hope that clarifies

Re: Show declining account balances

Anonymous — Mon, 05 Dec 2022 08:01:07 GMT

Hi KW123

You can create a measure :

Measure 2 = var a=FILTER(ALL('Table (4)'),'Table (4)'[Customer]=SELECTEDVALUE('Table (4)'[Customer]))

var b= MAXX(FILTER(a,'Table (4)'[Date]=MAXX(a,[Date])),[CB])

VAR c=MINX(FILTER(a,'Table (4)'[Date]=MINX(a,[Date])),[CB])

var d= DIVIDE(b-c,b,0)

return if(d<=-0.25,1,0)

then put the measure into the visual filter

The output

^{Best Regards,}

^{Yolo Zhu}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

Re: Show declining account balances

lbendlin — Mon, 05 Dec 2022 22:01:17 GMT

You say rolling 12 months but your sample data does not cover that interval.

Re: Show declining account balances

KW123 — Mon, 05 Dec 2022 23:30:28 GMT

Anonymous
Thank you for taking the time to reply!

Here is what I get if I enter in the DAX you have provided. I tried using the Dates table but it doesn't allow for that input. So I tried with the report month instead.

Re: Show declining account balances

KW123 — Mon, 05 Dec 2022 23:55:32 GMT

lbendlin

Date	Customer ID	Balance
11/30/2022	1	$700
11/30/2022	2	$987
11/30/2022	3	$600
11/30/2022	4	$2575
10/31/2022	1	$600
10/31/2022	2	$989
10/31/2022	3	$700
10/31/2022	4	$2500
09/30/2022	1	$400
09/30/2022	2	$990
09/30/2022	3	$800
09/30/2022	4	$3000
08/31/2022	1	$500
08/31/2022	2	$991
08/31/2022	3	$1000
08/31/2022	4	$2000
07/31/2022	1	$900
07/31/2022	2	$992
07/31/2022	3	$2000
07/31/2022	4	$1700
06/30/2022	1	$800
06/30/2022	2	$993
06/30/2022	3	$3000
06/30/2022	4	$1500
05/31/2022	1	$200
05/31/2022	2	$994
05/31/2022	3	$4000
05/31/2022	4	$1075
04/30/2022	1	$300
04/30/2022	2	$995
04/30/2022	3	$5000
04/30/2022	4	$1025
03/31/2022	1	$900
03/31/2022	2	$996
03/31/2022	3	$6000
03/31/2022	4	$1000
02/28/2022	1	$800
02/28/2022	2	$997
02/28/2022	3	$7000
02/28/2022	4	$500
01/31/2022	1	$600
01/31/2022	2	$998
01/31/2022	3	$8000
01/31/2022	4	$250
12/31/2021	1	$700
12/31/2021	2	$999
12/31/2021	3	$9000
12/31/2021	4	$100
11/30/2021	1	$500
11/30/2021	2	$1000
11/30/2021	3	$10000
11/30/2021	4	$25

With this example, Customer ID 3 would be shown and customer ID 4 would not be shown as it had an increase, and customer ID 1 had a decrease but it was less than 25%

I hope that this clarifies

Re: Show declining account balances

lbendlin — Tue, 06 Dec 2022 00:28:15 GMT

Your sample data is still not good.

Re: Show declining account balances

Anonymous — Tue, 06 Dec 2022 08:14:20 GMT

Hi KW123

Do you create it as a measure?

The picture you offerred displays that you creat it as a table or column

^{Best Regards,}

^{Yolo Zhu}

Re: Show declining account balances

KW123 — Sun, 11 Dec 2022 01:01:35 GMT

Hi Anonymous

I created it as a measure

Re: Show declining account balances

KW123 — Wed, 14 Dec 2022 22:52:53 GMT

Anonymous

I get the same Multiple table arguments error in a column as well

Re: Show declining account balances

KW123 — Wed, 14 Dec 2022 22:53:37 GMT

lbendlin

I used your DAX however it takes a very long time to load and says not enough memory. Is there any way to change the DAX a little bit?

Re: Show declining account balances

lbendlin — Wed, 14 Dec 2022 23:00:03 GMT

Certainly, but I would need to have sample data that illustrates the issue (including the slowness). You can also use DAX Studio to evaluate the query plan and make adjustments accordingly.

Re: Show declining account balances

Anonymous — Thu, 15 Dec 2022 09:36:41 GMT

Hi KW123

Try the code below:

Measure 2 = var a=FILTER(ALLSELECTED('Table'),'Table'[ Customer ID]=SELECTEDVALUE('Table'[ Customer ID])) var b= MAXX(FILTER(a,[Date]=MAXX(ALLSELECTED('Table 2'[Date]),[Date])),[ Balance]) VAR c=MINX(FILTER(a,[Date]=MINX(ALLSELECTED('Table 2'[Date]),[Date])),[ Balance]) var d= DIVIDE(b-c,b,0) return if(d<=-0.25,1,0)

then put the measure into the visual filter

^{Best Regards,}

^{Yolo Zhu}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

Re: Show declining account balances

KW123 — Fri, 30 Dec 2022 22:49:12 GMT

Thanks so much Anonymous This shows exactly what I need.

Is it possible to do this as a column? I need to break the data down even further and having it as a calculated column to filter the account numbers is better.

Thanks!