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 ...
v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft 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