cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rbeneteli
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
v-juanli-msft
Community Support
Community Support

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

7.png

 

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))

 

9.png

 

 

Best Reagrds

maggie

 

Ashish_Mathur
Super User
Super User

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Greg_Deckler
Super User
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.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

BizApps LATAM 2023

Business Application LATAM Summit 2023

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

Power Platform Bootcamp

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.