Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

SUMIFS Statement

Hi - not a power user and just barely an occasional user.  I'm trying to solve this project in some down time.  Ultimately I'm looking to replicate a nested IF/SUMIFS statement in BI.  And yes, I've already searched the forum for similar solutions, but due to my lack of experience in this program, it was very difficult to follow along.

 

Scenario:  70k+ rows of data with the following columns. DATE, DAYS FROM TODAY (calculation off of DATE), CAMPAIGN ID, IMPRESSIONS, CLICKS.  I'm trying to summarize data into the six following calculations: 

  • Impressions - 1 Day
  • Clicks - 1 Day
  • Impressions - 7 Days
  • Clicks - 7 Days
  • Impressions - 30 Days
  • Clicks - 30 Days

This data will then be used with another table that I've already created with a whole different set of data, using the CAMPAIGN ID as the link between the two tables. 

 

Here's what the data looks like (with all of the calculated data I'm looking to replicate): 

 

Since the Excel formauls are essentially all the same, just looking changing the "<31" to equate for the 1/7/30 time frames.  Here's the calculation for IMPRESSIONS - 30 DAYS

  • =IF(B2<31,SUMIFS(D:D,C:C,C2,B:B,"<31"),0)

I'm assuming this can be replicated in BI, I just don't know the correction function to use.  Any help to dumb this down for me would be greatly appreciated!

Need Help
Show and Tell

3 Replies

  • parry2k's avatar
    parry2k
    Super User
    7 years ago

    Anonymous best practice in PowerBI is to add date dimension in your model, there are many topics on how to add date dimension using DAX or power query. Once you have date dimension, all time intelligence related reporting becomes super easy with inbuilt time intellingence DAX functions.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks, parry2k !  One thing I forgot to add, which I'll do in the OP, is that these calcultions will be looked up via another table, which is why I'm looking for these fields to be added as a calculation to then be related to another data set.  Will your suggested solution still be able to help with this?