Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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)
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
Hi @smt9623
The following steps are for your reference.
My sample:
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])
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:
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.
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.
To have a year column like in the scenario, I first used the following formula
But when I try to convert the column in this table to date type, it returns to 1905
So I revert back to integer type
Then I thought I needed to link these two tables, so I linked my Year columns together (*:1)
Then I typed the formula last year sales
Then I also created the countAll metric, which returned me an empty value.
Thank you very much for your support
Can anyone help me?
I need help
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
146 | |
75 | |
64 | |
52 | |
47 |
User | Count |
---|---|
218 | |
88 | |
73 | |
64 | |
60 |