Forum Discussion

mz1290's avatar
mz1290
Regular Visitor
9 years ago

Sum data based on multiple date criteria

I'm trying to replicate a formula that I use in excel in PowerBI. The excel formula is a simple sumifs and is referencing a series of cells that dynamically populates the beginning date of each month for a range of consecutive months.

 

The formula logic is as follows: SUM revenue if order date is prior to beginning date of current month AND if billing date is during the current month.

 

Any ideas? I'm still very new in my PowerBI/Power Pivot journey.

Need Help

2 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    You can use filters in your SUM function, so something like 

     

    MyMeasure = CALCUATE(SUM('table'[Revenue]) , FILTER('Table' , Col1 > Col2 && Col3 >= <some dynamic value> ) )
  • DoubleJ's avatar
    DoubleJ
    Solution Supplier
    9 years ago

    Let's say you have this table:

     

     

     

     

     

     

     

     

    Then you can create this measure

    RevenueCalc = 
CALCULATE(
	SUM(Table1[Revenue]),
	Table1[OrderDate] <= EOMONTH(TODAY(),-1),
	MONTH(Table1[BillingDate]) = MONTH(TODAY())
)

    The first argument of the CALCULATE function is the field you want to sum up. The following arguments are the filters.

     

    The value of this measure with the sample data is 400 ( as we currently are in February 2017)

     

    Hope this helps

    JJ