Reply
Highlighted
Frequent Visitor
Posts: 2
Registered: ‎06-27-2017
Accepted Solution

Filtering by data trend

Hi all,

 

I'm new to the forums so forgive me if this isn't explained that well. I'm trying to work out how it would be possible to show businesses whose sales are on a downward trend. 

 

I have sales information for a large number of businesses and I use a measure to display their sales over the previous 12 months (12 month total sales) by month. What I would like to do is to identify those that have had declining sales for three straight months. I currently display the 12 month total sales in a table but have no way of sorting out the businesses in decline.

 

I've popped some dummy data in below. I don't know how to achieve it but I would like to be able to filter Bravo and Delta out of the data below.

 

 12month total sales  
Business NameJan-17Feb-17Mar-17
Alpha100012001000
Bravo150014001300
Charlie120011001200
Delta400300250
Echo100010001000
Foxtrot100012001400

 

Thanks in advance for your help. Let me know if you need anything else.


Accepted Solutions
Super Contributor
Posts: 3,697
Registered: ‎07-17-2016

Re: Filtering by data trend

Hi @arcadefire4,

 

If I understand you correctly, you should be able to follow steps below to sorting out the businesses in decline.

 

I assume you have a table called 'Table1' like below.

 

t1.PNG

 

1. Use the formula below to create a new calculate column in 'Table1'.

Is In Decline = 
VAR previousMonthSales =
    CALCULATE (
        SUM ( Table1[Sales] ),
        ALLEXCEPT ( Table1, Table1[Business Name] ),
        PREVIOUSMONTH ( Table1[Month] )
    )
VAR NextMonthSales =
    CALCULATE (
        SUM ( Table1[Sales] ),
        ALLEXCEPT ( Table1, Table1[Business Name] ),
        NEXTMONTH ( Table1[Month] )
    )
RETURN
    IF (
        Table1[Sales] < previousMonthSales
            && Table1[Sales] > NextMonthSales
            && NextMonthSales <> 0,
        1,
        0
    )

c1.PNG

 

2. Then you should be able to use the new create calculate column as Slicer or visual level filter to sorting out the businesses in decline.

r1.PNG

 

Here is the sample pbix file for your reference. Smiley Happy

 

Regards

View solution in original post


All Replies
Super Contributor
Posts: 3,697
Registered: ‎07-17-2016

Re: Filtering by data trend

Hi @arcadefire4,

 

If I understand you correctly, you should be able to follow steps below to sorting out the businesses in decline.

 

I assume you have a table called 'Table1' like below.

 

t1.PNG

 

1. Use the formula below to create a new calculate column in 'Table1'.

Is In Decline = 
VAR previousMonthSales =
    CALCULATE (
        SUM ( Table1[Sales] ),
        ALLEXCEPT ( Table1, Table1[Business Name] ),
        PREVIOUSMONTH ( Table1[Month] )
    )
VAR NextMonthSales =
    CALCULATE (
        SUM ( Table1[Sales] ),
        ALLEXCEPT ( Table1, Table1[Business Name] ),
        NEXTMONTH ( Table1[Month] )
    )
RETURN
    IF (
        Table1[Sales] < previousMonthSales
            && Table1[Sales] > NextMonthSales
            && NextMonthSales <> 0,
        1,
        0
    )

c1.PNG

 

2. Then you should be able to use the new create calculate column as Slicer or visual level filter to sorting out the businesses in decline.

r1.PNG

 

Here is the sample pbix file for your reference. Smiley Happy

 

Regards

Frequent Visitor
Posts: 2
Registered: ‎06-27-2017

Re: Filtering by data trend

Thanks very much, that's exactly what I was after!