March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
I have data which looks like this:
The first 4 columns are in a table named [orders]
The last column is in a table named [on_hand] this table also has the [org] and [code] field so it can link to the [orders] table.
What I would like is a new column as a running total which will subtract the [ord_qty] from the first [on_hand] total then each row from there based on [org], [code] & [date] (date ascending) have this running total continue to decrease.
So in the above the rows with 528 in [on_hand] should be 525 then 515, 514 and finally 512. I should not even see the 528 total because I need to deduct that first 3 from it.
I have no clue how to write this in DAX, any help would be greatly appreciated.
Solved! Go to Solution.
hi @mgiusto
Please try this
Step1 : Created tables based on data shared by you, please note that I have kept all datatypes as whole number except date which is Date Datatype. I have not linked these two tables in data model.
Step2 :
Create this measure.
hi @mgiusto
Please try this
Step1 : Created tables based on data shared by you, please note that I have kept all datatypes as whole number except date which is Date Datatype. I have not linked these two tables in data model.
Step2 :
Create this measure.
So this kinda works, as long as the data in my grid stays sorted exactly the same order, if the sort changes the running total goes bonkers. But here's the other problem this measure created. The time it takes for this DAX to run is less than ideal. My data table is 600 records and for this to load it takes over 20 seconds or so to see the grid appear.
I'm going to see if there is a way to create this running total on the DB side so the data is already in my table rather than trying to have it calc on the fly via a measure. Would you have any advice on how to do that?
hi @mgiusto
If its 600 records, it shouldn't take that much time. There are other options.
In power query, merge Orders and OnHand table on "org" and "code" and bring in OnHand total into Orders table.
You can use calculated column
---------------------------------------------
Or you can use a measure
---------------------------------------------
If above solution does not work for you and you still want SQL.
Select o.org, o.code, o.Order_Date, oh.on_hand_tot,
(oh.on_hand_tot - SUM(o.ord_qty) OVER(PARTITION BY o.org, o.code ORDER BY o.Order_Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) "Runningtot"
from Orders o
inner join OnHand oh
on o.org = oh.Org AND o.code = oh.code
Please note that running total is calculated per group(org and code) and in orderdate ascending order, if you change sort order of these three column, running total will still be correct but result will look like bonkers 😄 if that is not the case please share pbix file with same data as you shared in your first post.
can you post the sample pbix?
org | code | date | ord_qty | on_hand | Run Tot |
202 | 50662 | 3/8/2024 | 3 | 528 | 525 |
202 | 50662 | 3/14/2024 | 10 | 528 | 515 |
202 | 50662 | 3/15/2024 | 1 | 528 | 514 |
202 | 50662 | 3/20/2024 | 2 | 528 | 512 |
202 | 29314 | 3/15/2024 | 1 | 2142 | 2141 |
202 | 29314 | 3/20/2024 | 1 | 2142 | 2140 |
202 | 74021 | 2/6/2024 | 3 | 11770 | 11767 |
202 | 74021 | 2/16/2024 | 24 | 11770 | 11143 |
202 | 74021 | 2/28/2024 | 4 | 11770 | 11139 |
202 | 74021 | 2/29/2024 | 1 | 11770 | 11138 |
202 | 74021 | 3/12/2024 | 13 | 11770 | 11125 |
202 | 74021 | 3/20/2024 | 2 | 11770 | 11123 |
202 | 11293 | 3/21/2024 | 2 | 3336 | 3334 |
202 | 25796 | 3/19/2024 | 1 | 8 | 7 |
Here is some sample data, I have added the last column Run Tot to show the desired result.
Reminder, the On Hand is in a different table and does not exist in the [order] table.
RT =
var md = max('Table'[date])
return max('Table'[on_hand])-CALCULATE(sum('Table'[ord_qty]),'Table'[date]<=md)
You can adjust the formula to provide the on_hand lookup differently.
This does not work as it does not take into account that you need to match the [on_hand] records to the [ord_qty] records based on [org] and [code] being equal in both tables.
Here's what the two tables look like:
[orders]
org | code | date | ord_qty |
202 | 50662 | 3/8/2024 | 3 |
202 | 50662 | 3/14/2024 | 10 |
202 | 50662 | 3/15/2024 | 1 |
202 | 50662 | 3/20/2024 | 2 |
202 | 29314 | 3/15/2024 | 1 |
202 | 29314 | 3/20/2024 | 1 |
202 | 74021 | 2/6/2024 | 3 |
202 | 74021 | 2/16/2024 | 24 |
202 | 74021 | 2/28/2024 | 4 |
202 | 74021 | 2/29/2024 | 1 |
202 | 74021 | 3/12/2024 | 13 |
202 | 74021 | 3/20/2024 | 2 |
202 | 11293 | 3/21/2024 | 2 |
202 | 25796 | 3/19/2024 | 1 |
[on_hand]
org | code | on_hand |
202 | 50662 | 528 |
202 | 29314 | 2142 |
202 | 74021 | 11770 |
202 | 11293 | 3336 |
202 | 25796 | 8 |
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
27 | |
18 | |
16 | |
12 | |
11 |
User | Count |
---|---|
38 | |
29 | |
28 | |
20 | |
18 |