Forum Discussion

HSmith's avatar
HSmith
Regular Visitor
9 years ago

Declining Spend across Multiple Months

Hi,

 

I am trying to build a report which brings forward information about customers who we have continued to see a decline in spend each month for 3-4 months so that this can be used as a flag to see if customers are at risk of being lost. 

 

I'd like this to be able to filter out those who do not fulfill this requirement and then be able to drill down into detailed information for those who this does apply to.

 

Not real data below (this would link to multiple tables based on invoice number / date / measures etc) and in this example it would only pull forward ABC and BCD:

Invoice numberAccount CodeRevenueDate
1ABC4001/01/2016
4ABC3005/02/2016
8ABC2009/03/2016
11ABC1018/04/2016
2BCD8001/01/2016
5BCD4003/02/2016
6BCD2005/02/2016
9BCD5009/03/2016
12BCD2018/04/2016
3CDE5001/01/2016
7CDE6005/02/2016
10CDE7009/03/2016
13CDE6018/04/2016

 

 

Any help much appreciated,

 

Holly

 

 

1 Reply

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    9 years ago

    HSmith

     

    Hi,

     

    First, we can add a flag first. If the total of this month is smaller than that of last month, we add “-1”, or add “1”. This formula is generated according to your data.

     

    Flag =
VAR LastMonthSum =
    CALCULATE (
        SUM ( Table1[Revenue] ),
        FILTER (
            'Table1',
            'Table1'[Account Code] = EARLIER ( 'Table1'[Account Code] )
                && MONTH ( 'Table1'[Date] )
                    = ( MONTH ( EARLIER ( 'Table1'[Date] ) ) - 1 )
        )
    )
VAR CurrentMonthSum =
    CALCULATE (
        SUM ( Table1[Revenue] ),
        FILTER (
            'Table1',
            'Table1'[Account Code] = EARLIER ( 'Table1'[Account Code] )
                && MONTH ( 'Table1'[Date] ) = MONTH ( EARLIER ( 'Table1'[Date] ) )
        )
    )
RETURN
IF ( CurrentMonthSum < LastMonthSum, -1, 1 )

     

    Second, we can sum the flags of three month. There are a few questions.

    1. 3 – 4 months mean the latest 3 – 4 months or any 3 – 4 month?
    2. Are there any other tables that can change the total revenue?

     

    Suggestions: sum up the flag, if the total is -3, it’s a target.

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale