Product distribution exception from selected set of customers

[shineug]

Hi All,

Please help to create a measure that lists the customers who have not purchased the selected product, within the list of customers who had at least one product sale for the last 30 days.

We created a measure that will return customers within the last 30 days of sales

30 Days Customer Sales1 = CALCULATE(Sales[Gross Sales], FILTER(DIMTime,DIMTime[salesdate] >= TODAY() - 30 ))

And created another measure for no sales

30 Days No Sales 1 = if ([30 Days Customer Sales1]>0 ,0,1)

while using this measure power bi will list all the customer who doesnt have sales from the past years .

We need only the set of customers who are having sales for at least single product for the last 30 days.

For eg. lets say 1000 customers have sales for the last 30 days. Out of 1000 customers who all didnt purchase the selected product.

[shineug]

Thanks @Sergii24 , with filter option we are getting the desired results.

Is there any other option to achieve these results without using the filters?

[Sergii24]

There are following considertations to make:

• do you want your "within N days" calculation to be dynamic? You can achieve it by introducting a parameter that will allow you users to change N from 30 to any other number and the list of customers will be updated right away. In this case the answer is no, because you need to make such calcualtion within a measure and measure, as PowerBI object, can be applied as a filter only at visual level
• In case you don't need extra flexibility (i.e. always show 30days), then you can use calculated column. So the deifnition of "30days" will happen at the moment of the report refresh (let's say once a day). If you make a calculation as a column of any table, you can drag that column to filters pane on page or report level: in such way you apply the rule once for the whole page/report.
  • it's important to consider your semantic model: creation of calcualted column will make refresh time longer and the size of your pbix file bigger. I don't expect such impact to be significant as you would work on dimensional table of customers, the effect of adding 1 extra column with maybe 1000-10000 rows in Customer table is almost neglectable.

It's up tp you to choose 🙂 Good luck with your project!

[Sergii24 · Accepted solution]

Hi @shineug, does your measure "30 Days No Sales 1" work as you expect? If you create a visual "table" with customer name and "30 Days No Sales 1", does it correcly show 1 or 0? If so, to filter a visual by a measure you need to add it to "Filters on this visual": bring the measure there as well, then select "Filter type" - Advanced filtering = 1. This will filter the visual by values of your measure.

I hope it helps you!