Count sales for each month with conditions

[rbeneteli]

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

[v-juanli-msft]

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

[Ashish_Mathur]

Hi,

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

[Greg_Deckler]

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.