Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Solved! Go to Solution.
Hi @Anonymous ,
Try EARLIER function in the calculated column.
Eexpected Result = CALCULATE(SUM('Table'[RemSalesQty]),FILTER('Table',[RECID]<EARLIER('Table'[RECID])))
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
All - Below is an image from what I'm trying to do, filtered down to one particular ImportDate and Item to show the expected result. If I remove the statment underlined in red, I get 880 for all rows which is what I'd expect, but why can't I re-establish a filter using the RECID - I've tried using SELECTEDVALUE(),MIN() and all sorts of combinations to re-apply a filter using that row's RECID but nothing works.
Hi @Anonymous ,
Try EARLIER function in the calculated column.
Eexpected Result = CALCULATE(SUM('Table'[RemSalesQty]),FILTER('Table',[RECID]<EARLIER('Table'[RECID])))
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Here is a very small sample, I've included the field 'RemSalesQty' so you can see what I'm trying to sum:
ImportDate | CustAccount | Customer | ItemId | RecID | StartDate | RemSalesQty | SumRem |
12/1/2021 | 1494 | BOB | 1567 | 564106299 | 12/15/2021 | 4 | |
12/1/2021 | 1494 | BOB | 1567 | 564106300 | 12/27/2021 | 2 | 4 |
So when I use ADDCOLUMNS to add SumRem, I'd like to keep the filters on SalesOrderHistory coming from the current row values for ImportDate, CustAccount, Customer, and ItemID and sum the RemSalesQty for all RECID's that are LESS THAN the current row, so as you see 564106300 has a sum of 4. I've done this many times using measures, but now using ADDCOLUMNS with SUMMARIZE to create a Calculated Table, there seems to be an issue - it's as if I can't keep the filters on SalesOrderHistory given values from the current row.. I've tried ALL(SalesOrderHistory) and reapplying the filters one by one, didn't work. I've tried ALLEXCEPT(), and many other different combinations and I keep either getting blank for everything or it sums the entire table, no filters.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |