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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
stokesmcr
New Member

Formula Help (SUMIF not available)

Hi All,
Need some Power BI formula geniuses.
I have a formula which I have created in Excel and trying to replicate in Power BI, but unable due to SUMIF function not available.

General Gist of Formula.
1) Current Time Period is May.
2) I want to add - what I have invoiced for the last period + what I have orders for next period (see green)
3) See picture below how I currently have this executed in Excel, I cannot implement in Power BI.

Data below is dummy for sake of the exercise but I have a much larger data set in the background.

Please help!!

 

formulaissue.PNG

1 ACCEPTED SOLUTION
stokesmcr
New Member

Managed to resolve it.

 

Calculation = (CALCULATE(SUMX('Table'[Orders]),FILTER(Table,Table[Period]<>"APR 2016")) + (CALCULATE(SUMX('Table','Table'[Invoiced]),FILTER(Table,Table[Period]="APR 2016"))))

View solution in original post

4 REPLIES 4
stokesmcr
New Member

Managed to resolve it.

 

Calculation = (CALCULATE(SUMX('Table'[Orders]),FILTER(Table,Table[Period]<>"APR 2016")) + (CALCULATE(SUMX('Table','Table'[Invoiced]),FILTER(Table,Table[Period]="APR 2016"))))

GilesWalker
Skilled Sharer
Skilled Sharer

@stokesmcr - had this same issue the other day. This worked for me:

 

CALCULATE(SUMX('Table','Table'[Invoiced goods]+[Ordered goods]),FILTER(ALL('Table'),'Table'[Period]<=MAX('Table'[Period])))

 

Thanks should work for you. 'Table' is what ever your table/file name is.

 

Thanks,

 

Giles

@GilesWalker

 

Thanks for your help so far. How did you pass the time period requirement into the formula?

 

In my case - I want "Invoiced" for April and "Orders" for May + June. 

 

Thank you, Michael

matemusic
Advocate III
Advocate III

DAX formulas, which we use in Power BI have a little different logic. In your example, a think the best way is to use SUMX with FILTER function 

SUMX((FILTER(<the name of your table>,"APR 2016"=<name column you want to compare>),<name of column you want to sum>)

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

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