March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet 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
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!!
Solved! Go to Solution.
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"))))
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"))))
@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
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
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>)
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
121 | |
99 | |
90 | |
72 | |
62 |
User | Count |
---|---|
140 | |
121 | |
106 | |
98 | |
94 |