SUMIFS Statement

[Anonymous]

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!

[parry2k]

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

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? 

[parry2k]

@Anonymous you need to provide the full picture what you are trying to do. these measures will work depends how your  data model look like. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490