Removing slow moving items from Power BI visuals

Problem

There are times you will need to remove items which does not have any values for past few months. For example, you may want to remove items with no sales for the past 3 months from a visual.

As you can see different visual will give different results for the same method of solving the problem. e.g. Matrix vs Table visuals.

Source Data

Untitled

I also have a calendar table named MyDates.

Identifying Slow Moving Items

To identify the items with no sales for past 3 months, we can use the following measure:

NEW Sales For Past 3 Months :=
VAR MaxDate =
    CALCULATE ( MAX ( MyDates[Date] )ALLSELECTED ( MyDates ) )
RETURN
    CALCULATE (
        SUM ( ‘Slow Moving Products'[Value] ),
        DATESINPERIOD ( MyDates[Date], MaxDate-3MONTH )
    )

)

The advantage of the above measure is that by having a slicer for the calendar table, the period for the calculation can be selected by the user. This is achieved by using the ALLSELECTED() function in VAR MaxDate.

 

Removing The Slow Moving Items In Power BI Matrix Visual

Capture

Note that the date in the above slicer and matrix visual column is from the MyDates c

To remove the items that have no sales for past 3 months, add the measure ”Sales For Past 3 Months” to the Visual Level Filters section of the matrix properties. Then set it to ‘Show items when the value’ is great than zero.

Capture

This will result in the below screen.Capture

As you can see, Product 3 and Product 5 which had no sales for past 3 months has been removed from the matrix.

 

Power BI Table Visual

If we use a table visual with the same measure the results will look different.

Since we need to use the MyDates calendar Date field, the table will have all the dates as shown below.

Untitled

To remove the blank ‘Values’ in the Visual Level Filters for the Value set is to ‘not blank’.

Capture

The same measure “Sales For Past 3 Months” gives different result in a Table visual. It gives the values of the last 3 months sum of value for each month.

Untitled

To remove the rows which have no sales for past 3 months we repeat the same steps as mentioned for Matrix visual above.

Capture

Resulting Table visual will be as below where Product 3 and Product 5 are removed for all the months – this result is wrong!

Capture

Removing The Slow Moving Items In Power BI Table Visual

To get the same result as in the Matrix visual, that is to remove the Product 3 and Product 5 in months April, May and June where there were no sales for these two products, we will need to rewrite the measure as follow:

Sales For Past 3 Months =
VAR MaxDate =
    CALCULATE ( MAX ( MyDates[Date] )ALLSELECTED ( MyDates ) )
VAR MinDate =
    CALCULATE (
        MIN ( MyDates[Date] ),
        DATESINPERIOD ( MyDates[Date], MaxDate-3MONTH )
    )
VAR LAst3MOnthsSales =
    CALCULATE (
        SUM ( ‘Slow Moving Products'[Value] ),
        DATESINPERIOD ( MyDates[Date], MaxDate-3MONTH )
    )
RETURN
    IF (
        MIN ( MyDates[Date] ) < MinDate,
        MAX ( ‘Slow Moving Products'[Value] ),
        LAst3MOnthsSales
    )

Then repeat the same process in Visual Level Filters for removing rows that had no sales for past 3 months.

Capture

The resulting table is:

Untitled

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress.com.

Up ↑

%d bloggers like this: