Forum Discussion

rbeneteli's avatar
rbeneteli
Frequent Visitor
7 years ago
Solved

Sum Rows with conditions

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 ...
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

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

    sum_rows_with_conditions

    Best Regards,
    Dale