Forum Discussion

KW123's avatar
KW123
Helper V
3 years ago
Solved

Show declining account balances

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! 

  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

     

14 Replies

    • KW123's avatar
      KW123
      Helper V
      3 years ago

      lbendlin 

      Here is an example of what the data looks like 

      DateCustomer IDCustomer Balance
      11/30/2022123$535
      11/30/2022234$1045
      11/30/2022345$324
      10/31/2022123$525
      10/31/2022234$1025
      10/31/2022345$300
      09/30/2022123$948
      09/30/2022234$1000
      09/30/2022345$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 

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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.