Declining Spend across Multiple Months

[HSmith]

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 number	Account Code	Revenue	Date
1	ABC	40	01/01/2016
4	ABC	30	05/02/2016
8	ABC	20	09/03/2016
11	ABC	10	18/04/2016
2	BCD	80	01/01/2016
5	BCD	40	03/02/2016
6	BCD	20	05/02/2016
9	BCD	50	09/03/2016
12	BCD	20	18/04/2016
3	CDE	50	01/01/2016
7	CDE	60	05/02/2016
10	CDE	70	09/03/2016
13	CDE	60	18/04/2016

Any help much appreciated,

Holly

[v-jiascu-msft]

@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