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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
smt9623
Regular Visitor

How to calculate the number of customers with sales of more than 10 tons in the previous year

Hello experts

 

I have a table with customer ID, quantity and year data. There is also a slicer on my screen where I can select the year. When I select the year 2023, I need a formula that calculates the number of customers with more than 10000 KG sales in 2022.

 

I was able to write the DAX formula that brings the sales amounts of the year before the year I selected.

 

Last Year Sales = CALCULATE([Quantity ETM],SAMEPERIODLASTYEAR('Retained Customer'[Calender].[Date]))

 

With this formula it brings the sales for the previous year of the year I selected last year. In order to make it count for those with more than 10000 KG, I applied a formula like this;

 

LY >10 tons Sales = CALCULATE([Last Year Sales],FILTER(VALUES('Retained Customer'[Ordering]),[Last Year Sales]>10000))

 

In this way, when I put my measurements in a table and look at it, although last year's sales were more than 10000 kilos, for some sellers, for some reason I don't understand, the value is not written. (As in the image below)

 

smt9623_0-1716466477926.png

 

 

Last year I also tried this formula to count those with more than 10 tons of sales, but it did not count for some vendors in the same way.

 

LY>10ton Count =
COUNTROWS(
FILTER(
VALUES('Retained Customer'[Orderer]),
CALCULATE([Last Year Sales]) > 10000
)
)


In short, I would be grateful if you could show me how you can show me the number of those who sold more than 10 tons in the slicer for the year preceding the year of my choice.

 

I apologize for my English deficiencies.

Thanks in advance

 

 

 

4 REPLIES 4
v-xuxinyi-msft
Community Support
Community Support

Hi @smt9623 

 

The following steps are for your reference.

 

My sample:

vxuxinyimsft_2-1716535500235.png

 

Is [Quantity ETM] a measure? Since I don't know how your [Quantity ETM] is calculated. So I created a measure to calculate SUM just to match your scenario.

sum = SUM('Table'[quantity])

 

1. Create a table as the slicer

Slicer = VALUES('Table'[year])

vxuxinyimsft_0-1716535358056.png

 

2. Create two measures as follows

Last Year Sales = 
VAR _lastYear = CALCULATE([sum], FILTER('Table', YEAR('Table'[year]) = YEAR(SELECTEDVALUE(Slicer[year])) - 1))
VAR _count = CALCULATE(DISTINCTCOUNT('Table'[customer ID]), FILTER('Table', _lastYear > 10000))
RETURN
_count

 

countAll = SUMX(VALUES('Table'[customer ID]), [Last Year Sales])

 

Output:

vxuxinyimsft_1-1716535466206.png

 

If you still have questions, please provide some sample data and the expected result based on sample data so that I can better help you. How to provide sample data in the Power BI Forum - Microsoft Fabric Community Or show it as a screenshot or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.

 

Best Regards,
Yulia Xu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-xuxinyi-msft 

 

I appreciate you creating a data set and scenario for me.

 

I could not apply this solution, which seems to make sense, to my dataset.
Maybe I may have given incomplete information. The calendar column is day/month/year, and I am using year in the automatically generated hierarchy.

smt9623_0-1716559530409.png

To have a year column like in the scenario, I first used the following formula

Yıl = YEAR('Elde Tutulan Müşteri'[Takvim yılı / takvim ayı.Takvim yılı / takvim ayı Düzey 01.Anahtar])
 
Then I created a slicer table as you said.
smt9623_1-1716559679872.pngsmt9623_2-1716559706531.png

 

But when I try to convert the column in this table to date type, it returns to 1905

smt9623_3-1716559778490.png

So I revert back to integer type

smt9623_4-1716559830384.png

 

Then I thought I needed to link these two tables, so I linked my Year columns together (*:1)

smt9623_5-1716559878848.png

 

Then I typed the formula last year sales

smt9623_6-1716560125382.png
Last Year Sales =
VAR _lastYear = CALCULATE([Miktar ETM], FILTER('Elde Tutulan Müşteri', YEAR('Elde Tutulan Müşteri'[Yıl]) = YEAR(SELECTEDVALUE('Slicer'[Yıl])) - 1))
VAR _count = CALCULATE(DISTINCTCOUNT('Elde Tutulan Müşteri'[Sipariş veren.Sipariş veren Düzey 01.Anahtar]), FILTER('Elde Tutulan Müşteri', _lastYear > 10000))
RETURN
_count
 
But this measure returned me a null value
 
smt9623_7-1716560826524.png

Then I also created the countAll metric, which returned me an empty value.

 

Thank you very much for your support

Can anyone help me?

smt9623
Regular Visitor

I need help

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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