Forum Discussion

hawkeyes12's avatar
hawkeyes12
Frequent Visitor
6 years ago
Solved

DAX IF Sum Statement

Hello, I need help with creating a DAX Formula. I have four columns in my table called "Transactions": 1. Date 2. Fund ID 3. Frequency 4. Dividends   And I need to write a formula that does ...
  • ahmedoye's avatar
    ahmedoye
    6 years ago

    hawkeyes12 , let your measures be:

     

    1. DividendSum =  SUM(YourTable[Dividends])
    2. HighestDate = MAXX(ALL('Table'[end date 1]),'Table'[end date 1])
    3. MDividends = CALCULATE([DividendSum], FILTER(YourTable, YourTable[Frequency] = "M" && YourTable[Date] >= [HighestDate] -30))
    4. QDividends = CALCULATE([DividendSum], FILTER(YourTable, YourTable[Frequency] = "Q" && YourTable[Date] >= [HighestDate] -90))
    5. ADividends = CALCULATE([DividendSum], FILTER(YourTable, YourTable[Frequency] = "A" && YourTable[Date] >= [HighestDate] -365))
    6. DividendsUsed = SWITCH(TRUE(), YourTable[Frequency] = "M", [MDividends], YourTable[Frequency] = "Q", QDividends, YourTable[Dividends] = "A", ADividends)

    If this solves the problem, kindly mark my response as a solution.

ahmedoye's avatar
ahmedoye
Responsive Resident
6 years ago

Hi hawkeyes12 , I like to break down my DAX into pieces to make it easier to write, read and understand. This is what you should write, assuming the dates start counting from today:

 

  1. DividendSum =  SUM(YourTable[Dividends])
  2. MDividends = CALCULATE([DividendSum], FILTER(YourTable, YourTable[Frequency] = "M" && YourTable[Date] >= TODAY() -30))
  3. QDividends = CALCULATE([DividendSum], FILTER(YourTable, YourTable[Frequency] = "Q" && YourTable[Date] >= TODAY() -90))
  4. ADividends = CALCULATE([DividendSum], FILTER(YourTable, YourTable[Frequency] = "A" && YourTable[Date] >= TODAY() -365))
  5. DividendsUsed = SWITCH(TRUE(), YourTable[Frequency] = "M", [MDividends], YourTable[Frequency] = "Q", QDividends, YourTable[Dividends] = "A", ADividends)

If this solves the problem, kindly mark my response as a solution.

  • hawkeyes12's avatar
    hawkeyes12
    Frequent Visitor
    6 years ago

    Hi ahmedoye this is very helpful! If the dates don't start counting from today and instead start counting from the most recent date in the [Date] column, how would this change the formula? For example, this calculation will be performed in mid-March and the source file will include data from 2/28/2019 to 2/29/2020. The first date that we would want included in the 30, 90 and 365 day calculation is 2/29/2020.
    I am having a very hard time with the date functions so any guidance you may have is very appreciated!

    • ahmedoye's avatar
      ahmedoye
      Responsive Resident
      6 years ago

      hawkeyes12 , let your measures be:

       

      1. DividendSum =  SUM(YourTable[Dividends])
      2. HighestDate = MAXX(ALL('Table'[end date 1]),'Table'[end date 1])
      3. MDividends = CALCULATE([DividendSum], FILTER(YourTable, YourTable[Frequency] = "M" && YourTable[Date] >= [HighestDate] -30))
      4. QDividends = CALCULATE([DividendSum], FILTER(YourTable, YourTable[Frequency] = "Q" && YourTable[Date] >= [HighestDate] -90))
      5. ADividends = CALCULATE([DividendSum], FILTER(YourTable, YourTable[Frequency] = "A" && YourTable[Date] >= [HighestDate] -365))
      6. DividendsUsed = SWITCH(TRUE(), YourTable[Frequency] = "M", [MDividends], YourTable[Frequency] = "Q", QDividends, YourTable[Dividends] = "A", ADividends)

      If this solves the problem, kindly mark my response as a solution.