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

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.