cancel
Showing results for
Did you mean:
Frequent Visitor

## Count sales for each month with conditions

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

3 REPLIES 3
Community Support

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.

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

Super User

Hi,

Your question is not clear.  Share your datasets and also show the expected result.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User
So, in general you need to probably use a VAR statement to create a temp table to FILTER your table to IDs greater than the MAX of the current month minus 1. Would need sample/example date to be more specific.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors