Forum Discussion
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 ...
hawkeyes12 , let your measures be:
- DividendSum = SUM(YourTable[Dividends])
- HighestDate = MAXX(ALL('Table'[end date 1]),'Table'[end date 1])
- MDividends = CALCULATE([DividendSum], FILTER(YourTable, YourTable[Frequency] = "M" && YourTable[Date] >= [HighestDate] -30))
- QDividends = CALCULATE([DividendSum], FILTER(YourTable, YourTable[Frequency] = "Q" && YourTable[Date] >= [HighestDate] -90))
- ADividends = CALCULATE([DividendSum], FILTER(YourTable, YourTable[Frequency] = "A" && YourTable[Date] >= [HighestDate] -365))
- 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.
Greg_Deckler
Community Champion
Community ChampionYou likely want to use a SWITCH statement. And you will likely want to calculate your value using a SUMX statement with a FILTER although you could also use CALCULATE with a FILTER statement. So either:
SUMX(FILTER('Table',....),[Dividends])
or
CALCULATE(SUM([Dividends]),FILTER(...))
Hi - thank you for the response. Could you please explain what you mean by a SWITCH Statement?
