Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello my friends, I have a table that shows the month and the code that belongs to first sale in that month, like this:
MONTH | CODE |
August | 11306 |
July | 11062 |
June | 10851 |
May | 10620 |
and then I have a large table with a lot of data, and I have to show how many sales I had in August, the problem is, if I have a sale with a code between 10851 - 11062 (codes that represents the entire month of June), that appear in August, and this sale have appeard in July, it counts as a sale for August, but if this sale just appear, after June, in August it doesn't count as a sale.
I have to do that for all months of the year, so if I'm analyzing October I have to look at sales of August that appears in September and also in October
Thanks for helping
Hi @rbeneteli
Base on my understanding, your condition is
A code will be count as a sale in this month when it represents in the last three consecutive months away from this month.
Please look at my test,
create a measure
count over last three months = CALCULATE ( COUNT ( Sheet7[code1] ), TOPN ( 3, FILTER ( ALL ( Sheet7 ), [code1] = MAX ( [code1] ) && [month] <= MAX ( [month] ) && [month] > MAX ( [month] ) - 3 ), [month], ASC ) )
1.code(5) which represents in June, appears in August, and this sale have appeard in July, it counts as a sale for August
2.code (8) just appear, after June, in August it doesn't count as a sale
3.code(15) just appear, after August, in October it doesn't count as a sale (not consecutive months)
4.code(21) which represents in August, appears in October, and this sale have appeard in Setemper, it counts as a sale for August
Then the count of sales per month which match the condition should be
Measure
count = CALCULATE(COUNT(Sheet7[code1]),FILTER(ALLEXCEPT(Sheet7,Sheet7[month]),[count over last three months]=3))
Best Reagrds
maggie
Hi,
Your question is not clear. Share your datasets and also show the expected result.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |