Show declining account balances

[KW123]

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! 

[lbendlin]

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

[KW123]

@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 

[lbendlin]

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

[KW123]

@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

[lbendlin]

Your sample data is still not good.

[KW123]

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

[lbendlin]

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.

[v-xinruzhu-msft · Accepted solution]

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

[KW123]

Thanks so much @v-xinruzhu-msft  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! 

[v-xinruzhu-msft]

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

[KW123]

@v-xinruzhu-msft 
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. 

[v-xinruzhu-msft]

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}

[KW123]

@v-xinruzhu-msft 

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

[KW123]

Hi @v-xinruzhu-msft 

I created it as a measure