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...
Need Help
DoubleJ's avatar
DoubleJ
Icon for Solution Supplier rankSolution 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