Continued 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-yulgu-msft]

Hi @HSmith,

Have you achieved your requirement with my original advice? If so, please kindly mark the corresponding reply as an answer so that others having similar concern can find the solution more easily. If you still have any question, please feel free to ask. Thanks for your understanding.

Regards,
Yuliana Gu

[v-yulgu-msft]

Hi @HSmith,

Suppose you want to use a Table visual to display data records. Please see below steps in my test.

Based on the sample table you provided, I created serveral calculated columns.

Month =
Sheet1[Date].[MonthNo]

Total revenue for each month =
CALCULATE (
    SUM ( Sheet1[Revenue] ),
    ALLEXCEPT ( Sheet1, Sheet1[Account Code], Sheet1[Date].[Month] )
)

Total revenue last month =
LOOKUPVALUE (
    Sheet1[Total revenue for each month],
    Sheet1[Account Code], Sheet1[Account Code],
    Sheet1[Month], Sheet1[Month] - 1
)

Flag1 =
IF (
    Sheet1[Month] = 1,
    BLANK (),
    IF (
        LOOKUPVALUE (
            Sheet1[Total revenue for each month],
            Sheet1[Account Code], Sheet1[Account Code],
            Sheet1[Month], Sheet1[Month] - 1
        )
            > CALCULATE (
                SUM ( Sheet1[Revenue] ),
                ALLEXCEPT ( Sheet1, Sheet1[Account Code], Sheet1[Date].[Month] )
            ),
        0,
        1
    )
)

Whether Continued Declining =
IF (
    CALCULATE ( SUM ( Sheet1[Flag1] ), ALLEXCEPT ( Sheet1, Sheet1[Account Code] ) )
        = 0,
    "Yes",
    "No"
)

Then, I could use column [Whether Continued Declining] as a flag to filter those customers are at risk of being lost.

For your another requirement that drill down into detailed information, I'am afraid currently it is not a supported functionality in Power BI desktop.

Best regards,
Yuliana Gu