What IF parameter: using 2 as filter to extract some data

[Alex_192]

Hello everyone,

I am facing quite a complex issue (for me 🙂 ) and I would really appreciate some guidance !

I have this kind of data:

 Client name / Sales amount / Date of sales

Client A	10	01/01/2017
Client A	20	01/02/2017
Client A	30	01/05/2017
Client A	40	01/08/2017
Client A	50	01/09/2017
Client A	60	01/02/2017
Client A	10	01/03/2017
Client B	10	01/01/2017
Client B	20	01/02/2017
Client B	30	01/05/2017
Client B	40	01/08/2017
Client B	50	01/09/2017
Client B	60	01/10/2017
Client B	10	01/12/2017
Client C	10	01/01/2017
Client C	20	01/02/2017
Client C	30	01/02/2017
Client C	40	01/02/2017
Client C	50	01/02/2017
Client C	60	01/02/2017
Client C	10	01/02/2017

I would like to look at the inactive clients based on 2 dynamic "what if" parameter:

- one would be a period of month (from 0 to 12), being the last X month from today

- one would be the minimum sales per month (from 0 to 100), being the minimum sales level from which I want to consider the client inactive

Those two what if should "say": select all clients which have bought not more than "10" sales in the last "3"months

My idea is then to show on the same report the list of those client + what was their sales in the 12 months before the inactive period selected (in my previous example before the last "3" months)

I understood that what if paramaters can only be used in measures, and I don't see how to manage this like that...

Any kind of help would be very much appreciated 🙂

Thanks 

Alex

[v-yulgu-msft]

Hi @Alex_192,

"What IF" parameter is not able to filter data. In your scenario, I would suggest you add a relative slicer from which users can choose a period of month.

Create a "What IF" parameter which is used to specify the minimum sales per month.

Create some measures similar to below:

Sum per month =
CALCULATE (
    SUM ( Client_Sales[Sales Amount] ),
    ALLEXCEPT (
        Client_Sales,
        Client_Sales[Client Name],
        Client_Sales[Date of Sales].[Month]
    )
)

minmum sales = IF([Sum per month]>=Parameter[Parameter Value],1,0)

Add above measure [minimum sales] into visual level filters, set its value to 0. In that case, once you specify a value for slicer and "What IF" parameter, the visual will display all clients which have bought not more than specified sales in the last X months.

Best regards,

Yuliana Gu

[Alex_192]

Hi @v-yulgu-msft,

Thank you very much for your answer, I think it does exactly what I asked for !

edit: after a few test, it doesnt' seem to work properly

But I think I am missing something, let me explain:

with this view I am able to see:

- all client who bought less than "5k" over the last "3" months period 

but I think I am not able to detect:

- a client who used to buy before this 3 months period and who became inactive during the last 3 months

- what amount of sales this client was doing on a 12 months period before those "3 months"

Do you think that's feasible ?

I found an other solution which basically consist in creating a client classification based on their sales done on previous quarters and then allocate e a status as "Inactive since 1Q" "Inactive since 2Q" ....

But with this solution I can't play with the period (it has to be Q-1; Q-2; Q-3;...) and it's really white or black, either the client bought something either it's 0... I can't play with a variable like "who bought less than xx in the last 3 months" to tag this client as an "inactive".

I don't know if I am clear enough, anyway thank you in advance for your time I really appreciate

Kind regards,

Alexandre