Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rrafferty37
Helper I
Helper I

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

Try this

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

View solution in original post

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

This work?

View solution in original post

9 REPLIES 9
Cmcmahan
Resident Rockstar
Resident Rockstar

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.

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. 

Try this

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

Hi @MitchM I have a similar query but Power BI's not letting me post any image here idk why, and I am really stuck please help!

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!

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

This work?

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!

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.