cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
rrafferty37 Regular Visitor
Regular Visitor

Count of customers that have increased/decreased YTD

Hello,

 

I have a table with 3 measures: LYTD Net Sales, YTD Net Sales, and YTD Variance

:Table1.JPG

 

I want to count how many customers (based on the current filters applied) have increased or decreased YTD.

 

YTD Net Sales = TOTALYTD([Net Sales], 'Date'[Date])
LYTD Net Sales = 
var first_date = STARTOFYEAR('Date'[Date])-365
var last_date = LASTDATE(Sales_TY_LY_PY[yyyymmdd])-365
return
CALCULATE([YTD Net Sales],
DATESBETWEEN('Date'[Date], first_date, last_date))
YTD Variance = [YTD Net Sales] - [LYTD Net Sales]

I am just not able to come up with the right solution, I have checked a number of threads that have had similar questions and tried to adapt those solutions but am coming up short. 

 

Thank you for any help or guidance you can provide!

2 ACCEPTED SOLUTIONS

Accepted Solutions
MitchM Member
Member

Re: Count of customers that have increased/decreased YTD

Try this

CountofGrowthCompanies = 
CALCULATE(
    COUNTROWS( SUMMARIZE( Table1, Table1[company] )),
    FILTER( SUMMARIZE( Table1, Table1[company] ),[Variance] > 0)
)

View solution in original post

MitchM Member
Member

Re: Count of customers that have increased/decreased YTD

SumofGrowthCompanies = 
    CALCULATE( 
        [Variance],
        FILTER( SUMMARIZE( Table1, Table1[company] ),[Variance] > 0)
    )

This work?

View solution in original post

8 REPLIES 8
Super User
Super User

Re: Count of customers that have increased/decreased YTD

It should look something like this:

IncreasedYTD = CALCULATE(DISTINCTCOUNT(Sales[BillingID]), FILTER(Sales, [YTD Variance] > 0) )

This is getting a distinct count of each group you're billing (use BilltoName or other unique identifier if you don't have an ID) but only where the [YTD Variance] is greater than zero.  You can use a similar measure with a flipped logical operator to get the count of those where it's less than zero.

rrafferty37 Regular Visitor
Regular Visitor

Re: Count of customers that have increased/decreased YTD

Hi @Cmcmahan , thanks for the reply!

 

I had tried something similar and have tried your method.  I am running into this problem:

Table3.JPG

 

Customers that have not purchased LYTD or YTD (So they purhcased either in July say, or not in the last 2 years), and then the YTD increased <0 is still counting them as well.  Basically it is providing a distinctount of my customers and ignoring the YTD variance filter. Not sure why that is happening. 

MitchM Member
Member

Re: Count of customers that have increased/decreased YTD

Try this

CountofGrowthCompanies = 
CALCULATE(
    COUNTROWS( SUMMARIZE( Table1, Table1[company] )),
    FILTER( SUMMARIZE( Table1, Table1[company] ),[Variance] > 0)
)

View solution in original post

rrafferty37 Regular Visitor
Regular Visitor

Re: Count of customers that have increased/decreased YTD

That's brilliant! Worked like a charm, I didn't think to use summarize. Thank you so much!

rrafferty37 Regular Visitor
Regular Visitor

Re: Count of customers that have increased/decreased YTD

Before I post another question, I am having some trouble summing a measure based on your solution.

 

Basiically for any row that is counted (The YTD variance is > 0), I want to sum the value of that difference.  

So the measure would give me the value of sales from customers that have increased YTD vs last year. 

Table 4.JPG

I added the previous measure you posted just to ensure it was working correctly. 

Have tried us the hasonefilter method, using logical operators, and sumx in various ways to try and achieve the desired results.  I am coming up short. 

 

Thanks!

MitchM Member
Member

Re: Count of customers that have increased/decreased YTD

SumofGrowthCompanies = 
    CALCULATE( 
        [Variance],
        FILTER( SUMMARIZE( Table1, Table1[company] ),[Variance] > 0)
    )

This work?

View solution in original post

rrafferty37 Regular Visitor
Regular Visitor

Re: Count of customers that have increased/decreased YTD

Thought it didn't work, realized there was an issue with my LYTD calculations due to date table having dates in the future. Fixed the calculations and added in a filter to show only dates from today or earlier and this did the trick. You are awesome, thank you so much!

MitchM Member
Member

Re: Count of customers that have increased/decreased YTD

Glad to help! Lord knows these boards have saved me more than a few times.

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 135 members 1,430 guests
Please welcome our newest community members: