Sum data based on multiple date criteria

[mz1290]

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.

[DoubleJ]

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

[Phil_Seamark]

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

MyMeasure = CALCUATE(SUM('table'[Revenue]) , FILTER('Table' , Col1 > Col2 && Col3 >= <some dynamic value> ) )