Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mz1290
Frequent Visitor

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.

2 REPLIES 2
DoubleJ
Solution Supplier
Solution Supplier

Let's say you have this table:

01.PNG

 

 

 

 

 

 

 

 

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
Employee
Employee

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

 

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.