Forum Discussion

Buckeye_Amy's avatar
Buckeye_Amy
Frequent Visitor
3 years ago
Solved

DAX Sumif Filtered Amount Between Date

Hi!

I am a novice DAX user struggling to write the equivalent of SUMIF in Excel in DAX. I am currently working with DAX in the Excel Data Model environment. The dataset am I working with is contributions and distributions from/to multiple investors in multiple investments. The data table includes both contributions and distributions. The goal is to ultimately calculate the long-term/short-term nature of gain and loss per investor per investment on a first-in first-out basis.

 

I am stuck on calculating the long-term contributions related to a particular(singular) distribution. Long-term contributions are those contributions the occured 366 days before the date of distribution. For example, the long-term contributions for Investor LP0001 in InvA related to the distribution on 2/9/2021 (row 4 below) equals $500,000 ($100,000 contribution on 5/14/209 and $400,000 contribution on 6/7/2019).

 

I have created a long-term holding date calculated field, but I'm not sure that's even necessary. I'm open to all options.

 

Thank you in advance! 🙂

 

Link to Sample Data

 

 

dates
DAX
sumifs
  • Martin_D's avatar
    Martin_D
    3 years ago

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

            'Data'[InvestTranType] = "Contribution"

        ),
        [TranAmount]
    )

     

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

     

    You can download the file here: sumif.pbix

    BR

    Martin

3 Replies

  • Martin_D's avatar
    Martin_D
    Solution Sage
    3 years ago

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

            'Data'[InvestTranType] = "Contribution"

        ),
        [TranAmount]
    )

     

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

     

    You can download the file here: sumif.pbix

    BR

    Martin

    • Buckeye_Amy's avatar
      Buckeye_Amy
      Frequent Visitor
      3 years ago

      Hi Martin,

       

      Quick follow-up. It appears that this solution may be causing circular dependency issues in subsequent calucated columns. Are there any changes we can make to the DAX to prevent circular issues?

       

      Thanks so much!