Running Total based on a Seperate Value

[LanceTDL]

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. 

[Jihwan_Kim · Accepted solution]

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

[LanceTDL]

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. 

[Jihwan_Kim]

Hi, @LanceTDL 

Sorry that I quite do not understand your question.

Please let me know how your desired outcome looks like.

Thanks.

[LanceTDL]

Sorry @Jihwan_Kim I tried to insert a table but I kept getting errors. 

My disered output would be like this: 


etc...

It has the daily value for any given date. 

[Jihwan_Kim]

Hi, @LanceTDL 

I am not 100% sure if I understood your explanation correctly, but please check the link down below.

https://www.dropbox.com/s/cne3sh5r1mz30ip/lance.pbix?dl=0 

[LanceTDL]

Thanks @Jihwan_Kim 

That was exactly what I was looking for!