DAX Sumif Filtered Amount Between Date

Hi Buckeye_Amy ,

The equivalent of Excel SUMIF in DAX is a combination of SUMX and FILTER. The following measure solves your requirement based on the table structure provided on sheet "Data" in your Excel file:

Long Term Contributions = 
VAR _LongtermContributionsDaysOffset = 366

VAR _AmountOfLongTermContributions =
    ADDCOLUMNS (
        CALCULATETABLE (
            SUMMARIZECOLUMNS (
                'Data'[InvestorNo],
                'Data'[Investment],
                'Data'[InvestTranDate]
            ),
            KEEPFILTERS ( 'Data'[InvestTranType] = "Distribution" )
        ),
        "@AmountOfLongTermContributions",
        SUMX (
            FILTER (
                ALL ( 'Data' ),
                'Data'[InvestorNo] = EARLIER ( [InvestorNo] ) &&
                'Data'[Investment] = EARLIER ( [Investment] ) &&
                'Data'[InvestTranDate] <= EARLIER ( [InvestTranDate] ) - _LongtermContributionsDaysOffset + 1 &&
                'Data'[InvestTranType] = "Contribution"
            ),
            [TranAmount]
        )
    )
RETURN
SUMX ( _AmountOfLongTermContributions, [@AmountOfLongTermContributions] )

The part replacing the SUMIF is:

SUMX (
    FILTER (
        ALL ( 'Data' ),
        'Data'[InvestorNo] = EARLIER ( [InvestorNo] ) &&
        'Data'[Investment] = EARLIER ( [Investment] ) &&
        'Data'[InvestTranDate] <= EARLIER ( [InvestTranDate] ) - _LongtermContributionsDaysOffset + 1 &&

    ),
    [TranAmount]
)

The result looks like (. and , will switch based on locale settings):

You can download the file here: sumif.pbix

BR

Martin