cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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

:

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
Resolver II

Try this

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

This work?

9 REPLIES 9
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.

Helper I

Hi @Cmcmahan , thanks for the reply!

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

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.

Resolver II

Try this

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

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!

Helper I

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.

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!

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

This work?

Helper I

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!

Resolver II

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

Helper I

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