cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hawkeyes12
Frequent Visitor

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 the following:

  • If [Frequency]="M" then sum the [Dividends] Column for the last 30 days
  • If [Frequency]="Q" then sum the [Dividends] Column for the last 90 days
  • If [Frequency]="A" then sum the [Dividends] Column for the last 365 days

Could someone please help me with this? I am new to DAX and am lost on whether I should use the Calculate formula, IF formula, etc.

1 ACCEPTED SOLUTION

Accepted Solutions
ahmedoye Resolver I
Resolver I

Re: DAX IF Sum Statement

@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.

View solution in original post

6 REPLIES 6
Super User IV
Super User IV

Re: DAX IF Sum Statement

You 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(...))


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

hawkeyes12
Frequent Visitor

Re: DAX IF Sum Statement

Hi - thank you for the response. Could you please explain what you mean by a SWITCH Statement?

ahmedoye Resolver I
Resolver I

Re: DAX IF Sum Statement

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.

Highlighted
hawkeyes12
Frequent Visitor

Re: DAX IF Sum Statement

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 Resolver I
Resolver I

Re: DAX IF Sum Statement

@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.

View solution in original post

hawkeyes12
Frequent Visitor

Re: DAX IF Sum Statement

@ahmedoye Thank you!

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors