Forum Discussion
Running Total based on a Seperate Value
Hello All,
I am a new user to Power BI, I am familiar with SQL, but still learning DAX. I am struggling to find any answers on the issue I am having. I have transactional data in SQL that I have connected to Power BI, my issue is the data doesn't contain the daily values, for this example daily qty on hand. What I want to have is either a column or measure of what the daily qty on hand was . The data I have is the current value qty on hand (as of today) and all the transactional history the items.
These are stored in two different but related tables
| Item | Qty on Hand |
| XYZ | 10 |
| Item | Transactional Qty | Date |
| XYZ | 1 | June 1, 2021 |
| XYZ | -1 | May 17, 2021 |
| XYZ | -4 | May 2, 2021 |
| XYZ | 3 | April 30, 2021 |
| XYZ | 2 | April 23, 2021 |
| XYZ | -5 | April 2, 2021 |
| XYZ | -1 | March 28, 2021 |
| XYZ | -1 | March 18, 2021 |
How can I generate either a measure or column that would display the date and the running total of qty on hand? Example for June 1, 2021 the Running Qty on hand would = (Qty on hand + transactional qty) in this case 10+1=11
This is an example of what I would like to see:
| Item | Running Qty On Hand | Date |
| XYZ | 11 | June 1, 2021 |
| XYZ | 10 | May 17, 2021 |
| XYZ | 6 | May 2, 2021 |
| XYZ | 9 | April 30, 2021 |
| XYZ | 11 | April 23, 2021 |
| XYZ | 6 | April 2, 2021 |
| XYZ | 5 | March 28, 2021 |
| XYZ | 4 | March 18, 2021 |
Thanks in advance for any assistance.
Hi, LanceTDL
Please check the below picture and the sample pbix file's link down below.
Running Qty On Hand Measure =
VAR transactioncumulatereverse =
CALCULATE (
SUM ( Transactions[Transactional Qty] ),
FILTER ( ALL ( Dates ), Dates[Date] >= MAX ( Dates[Date] ) )
)
RETURN
IF (
ISFILTERED ( Dates[Date] ),
IF (
NOT ISBLANK ( SUM ( Transactions[Transactional Qty] ) ),
transactioncumulatereverse
+ SUMX ( Transactions, RELATED ( Items[Qty on Hand] ) )
)
)https://www.dropbox.com/s/cne3sh5r1mz30ip/lance.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
6 Replies
Hi, LanceTDL
Please check the below picture and the sample pbix file's link down below.
Running Qty On Hand Measure =
VAR transactioncumulatereverse =
CALCULATE (
SUM ( Transactions[Transactional Qty] ),
FILTER ( ALL ( Dates ), Dates[Date] >= MAX ( Dates[Date] ) )
)
RETURN
IF (
ISFILTERED ( Dates[Date] ),
IF (
NOT ISBLANK ( SUM ( Transactions[Transactional Qty] ) ),
transactioncumulatereverse
+ SUMX ( Transactions, RELATED ( Items[Qty on Hand] ) )
)
)https://www.dropbox.com/s/cne3sh5r1mz30ip/lance.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
Thanks Jihwan_Kim
That was exactly what I was looking for!Is there a modification what would also allow skipped dates to carry the value from before. Basically that the transaction change is 0 for every day between 6/1/21 and 5/17/20 where there are no transactions.
I am having trouble graphing it since there the dates aren't consistent or all there. I would like to have a Date Hierarcy that I can break down by Year, Qtr, Month, Day of the stock levels.Hi, LanceTDL
Sorry that I quite do not understand your question.
Please let me know how your desired outcome looks like.
Thanks.
