How to filter those customer sales increase base on LYTD vs YTD sales ?

[Anonymous]

Hi All

Can some one share with me how to create a measure to filter those customer sales increase :-

https://www.dropbox.com/s/zj06hamc6kzxe78/PBT_V01021%20Filter%20sales%20increase%20customer%20listin...

Above link is my PBI file 

Paul

[AlB · Accepted solution]

@Anonymous 

You can create a measure to filter the visual

1.  Create this measure:

ShowMeasure = 
VAR salesYTD_ = [Sales YTD]
VAR salesLYTD_ = [Sales LYTD]
RETURN
IF(salesYTD_ > salesLYTD_, 1, 0)

 2.  Place [ShowMeasure] as a visual filter and choose to show when value is 1

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB · Accepted solution]

@Anonymous 

See it all at work in the attached file. Page 2

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB · Accepted solution]

@Anonymous 

Yes, that looks good. But bear in mind that what you are doing there is not exactly the YTD as you were doing before but the last 12 months and the 12 months previous to the last 12 months

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

@Anonymous 

As you have it now, the visual shows Sales YTD and Sales LYTD. And we are comparing those and selecting the customers that bought more in YTD than in LYTD. Now you say yo want to compare based on sales rather than (L)YTD.

1. Would the table visual still have YTD and LYTD in it, or sales?

2. As of today (23/12), you would still want to show the customers that had more sales in the period 01/01/2020- 23/12/2020 than in the period 01/01/2019- 23/12/2019, correct?

3. In January 2021, let's say 15/01/2020, you will the visual to show customers that had more sales in the period 01/01/2020- 31/12/2020 than in the period 01/01/2019- 31/12/2019, correct? 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Hi AIB

Thank you for sharing.

1. Would the table visual still have YTD and LYTD in it, or sales? Answer = Only using sales. not more YTD and LYTD

2. As of today (23/12), you would still want to show the customers that had more sales in the period 01/01/2020- 23/12/2020 than in the period 01/01/2019- 23/12/2019, correct? Answer : Yes correct.

3. In January 2021, let's say 15/01/2020, you will the visual to show customers that had more sales in the period 01/01/2020- 31/12/2020 than in the period 01/01/2019- 31/12/2019, correct? Answer Yes

This morning Amit have share with me using rilling last 12 month expression :-

_LAST12 = CALCULATE(sum(SALES[sales]),DATESINPERIOD('Date'[Date],MAX(SALES[date]),-12,MONTH))

Also the next 12 month rolling amount :-

_LAST24 = CALCULATE(SUM(SALES[sales]), DATESINPERIOD('Date'[Date], maxx('Date', DATEADD('Date'[Date],-12,MONTH)),-12, MONTH))

With above 2 expression ,  i can create a ShowMeasure field to filtre those sales up  :-

_SALES_UP =

VAR salesYTD_ = [_LAST12]

VAR salesLYTD_ = [_LAST24]

RETURN

IF(salesYTD_ > salesLYTD_, 1, 0)

Do you think this is right approach ?  As i cannot think of how to make use of sales field to compute the sales up and down.

Paul Yeo

[AlB]

@Anonymous 

Ok, but how exactly do you want the check done with sales? How do you want it now and how do you want it inJan 2021?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Hi AIB

Very sorry , I cannot understand your question.

Paul

[AlB]

@Anonymous 

I don't understand the question

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Hi AIB

The dis-advantage of using YTD and LYTD to compute the sales drop by customer is :-

- During next year the table will not reflect the whole year compare of customer sales. as it only compare Jan 2021 vs 2020 Jan sales.

If i am using SALES_ FIELD amount to compute the sales increase customer. Then the report will be also valid during Jan 2021. Because it have full year data during 2021 Jan.

Hope you understand.

Paul Yeo

[Anonymous]

I am puting this post to the top , because i am working on it now

[AlB · Accepted solution]

@Anonymous 

See it all at work in the attached file. Page 2

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Hi AIB

After thinking over , i should not using YTD for filter sales up customer , instead i should use SALES field , so that Jn 2021 , i am still able to check which are customer sales up compare 2019 vs 2020.

https://www.dropbox.com/s/ci6pvpsjfejx0yr/PBT_V01021%20Sales%20field%20for%20list%20sales%20increase...

Above link is my PBI file , hope that you can share with me.

Paul

[AlB · Accepted solution]

@Anonymous 

You can create a measure to filter the visual

1.  Create this measure:

ShowMeasure = 
VAR salesYTD_ = [Sales YTD]
VAR salesLYTD_ = [Sales LYTD]
RETURN
IF(salesYTD_ > salesLYTD_, 1, 0)

 2.  Place [ShowMeasure] as a visual filter and choose to show when value is 1

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Hi AIB

Thank you this expression is easy to understand.. i can modify to get those sales drop customer too.

Paul

[AlB]

Hi @Anonymous 

Which customers do you want to show exactly? Those who have a sales increase and had sales the previous years?  Would yo like to show "CASH SALES" customer? Why not?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Hi AIB

Uou spoted the important point , Yes i also like to include those customer last year not buying and YTD buying 

Paul