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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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