Sum Rows with conditions

[rbeneteli]

Hello,

I have a database that show each sale for each month like this one:

MONTH	CODE	VALUE
May	897	$ 100,00
May	901	$ 100,00
May	950	$ 110,00
May	999	$ 110,00
May	1001	$ 100,00
May	1003	$ 100,00
June	910	$ 100,00
June	955	$ 100,00
June	960	$ 100,00
June	1101	$ 110,00
June	1125	$ 110,00
June	1132	$ 110,00
June	1152	$ 100,00
July	953	$ 110,00
July	960	$ 100,00
July	1101	$ 100,00
July	1130	$ 100,00
July	1132	$ 100,00
July	1152	$ 110,00
July	1153	$ 110,00
August	955	$ 100,00
August	960	$ 100,00
August	1101	$ 110,00
August	1102	$ 100,00
August	1140	$ 100,00
August	1180	$ 110,00
August	1230	$ 100,00
August	1225	$ 100,00

My main task is to sum the values of sale in each month, but i have some conditions, when a I look for August as example, I need first to look at the code of that sale, if this code is greater then the base code from July it counts as a sale. But if it is greater then the code of June it will count as a sale only if it have occurred in July, here is the other table that show the base codes. If the code is smaller than June it will not count as sale in August.

MONTH	bCODE
May	950
June	1101
July	1132
August	1187

Analyzing the first data that i have shown I did manually the analysis for july and august, and I should sum the rows that have a number 1 in the new column as it follow:

MONTH	CODE	VALUE
May	897	$ 100,00
May	901	$ 100,00
May	950	$ 110,00
May	999	$ 110,00
May	1001	$ 100,00
May	1003	$ 100,00
June	910	$ 100,00
June	955	$ 100,00
June	960	$ 100,00
June	1101	$ 110,00
June	1125	$ 110,00
June	1132	$ 110,00
June	1152	$ 100,00
July	953	$ 110,00	0
July	960	$ 100,00	1
July	1101	$ 100,00	1
July	1130	$ 100,00	1
July	1132	$ 100,00	1
July	1152	$ 110,00	1
July	1153	$ 110,00	1
August	955	$ 100,00	0
August	960	$ 100,00	0
August	1101	$ 110,00	1
August	1102	$ 100,00	0
August	1140	$ 100,00	1
August	1180	$ 110,00	1
August	1230	$ 100,00	1
August	1225	$ 100,00	1

So I can get a visual like that one in PBI:

Month	Value
January	x
February	x
March	x
April	x
May	x
June	x
July	620
August	520
September	x
October	x
November	x
December	x

Just to clarify, when I was analysing December I should look for Code greater than November, or greater than October but that happened in November.

[Greg_Deckler]

This will be far easier if you have a numeric month sort column, do you have one of those or are you able to add one in your query?

[rbeneteli]

Yes, I already have a numeric month column

[v-jiascu-msft · Accepted solution]

Hi @rbeneteli,

Please check out the demo in the attachment. I believe you have a date table. The [Measure 2] is only for test purpose. 

Measure =
VAR lastMonthCode =
    CALCULATE ( MIN ( 'Table2'[bCODE] ), PREVIOUSMONTH ( 'Calendar'[Date] ) )
VAR last2Monthcode =
    CALCULATE (
        MIN ( 'Table2'[bCODE] ),
        DATESINPERIOD (
            'Calendar'[Date],
            EOMONTH ( MAX ( 'Calendar'[Date] ), -2 ),
            -1,
            MONTH
        )
    )
VAR last2MonthCodes =
    CALCULATETABLE (
        VALUES ( Table1[CODE] ),
        PREVIOUSMONTH ( 'Calendar'[Date] ),
        'Table1'[CODE] >= last2Monthcode
    )
RETURN
    CALCULATE (
        SUM ( Table1[VALUE] ),
        FILTER (
            'Table1',
            'Table1'[CODE] >= lastMonthCode
                || Table1[CODE] IN last2MonthCodes
        )
    )

Best Regards,
Dale