Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RONN0014
Frequent Visitor

Cumulative results by stock in row

Hi all, newbie in Powerbi. Would like some help with an issue i am facing.

 

To provide some context, I have a table with the following columns. 1.[Transaction Date], 2.[Transaction Type] (Buy/Sell) 3.[Stock Name]. What i am trying to achieve is to compute the cumulative stock as at the respective transaction date.

This can be done with the summit formula in excel, but I am not sure how this work in PowerBI. Have tried to Google and find answers in forum, but i can't seem to get it to work. Any advice is appreciated. Thank you!

1 ACCEPTED SOLUTION

@RONN0014 

 

Try this calculated column

 

Column = SUMX(FILTER('Table','Table'[Transaction Date]<=EARLIER('Table'[Transaction Date])&&'Table'[Stock Name]=EARLIER('Table'[Stock Name])),IF('Table'[Transaction Type]="Buy",1*'Table'[Transacted units],-1*'Table'[Transacted units]))

Capture.JPG

 



Did I answer your question? Mark my post as a solution!
Appreciate with a kudos
🙂

Here is my latest blog

https://community.powerbi.com/t5/Community-Blog/Dynamic-Page-Navigation-Based-on-User-Login/ba-p/109...


Regards,
Nandu Krishna

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

 

Hi, I also tried to find the solution of your problem. Please find below screenshot and DAX Expression.

 

Commulative_Figure_Till_Date =
VAR TransactionDate = 'Table'[Transaction Date]
VAR Comm_sum =
CALCULATE (
SUM ( 'Table'[Transacted units] ),
FILTER (
'Table',
'Table'[Transaction Date] <= TransactionDate
&& 'Table'[Transaction Type] = "Buy"
)
)
RETURN
IF (
'Table'[Transaction Type] = "Buy",
Comm_sum,
Comm_sum
- CALCULATE (
SUM ( 'Table'[Transacted units] ),
'Table'[Transaction Date] = TransactionDate
)
)

 

commulative sales buy and sell.JPG

 

 

Please mark it as solution if your like it.

 

Thanks,

Kaushlendra Mishra 

PowerBI Talks

Anonymous
Not applicable

By the way.... Using CALCULATE (like some propose in this thread) on a big fact table IS NOT THE BEST IDEA. Such code will be slow. Extremely slow because context transition is EXPENSIVE. Such code might not even return and it'll consume a lot of resources.

So, please stay away from such code.

Best
D
nandukrishnavs
Super User
Super User

@RONN0014 

 

Can you provide sample table (Not image) and expected output?


Regards,
Nandu Krishna

Hi @nandukrishnavs,

 

Please refer to the expected table below.

Transaction DateTransaction TypeTransacted unitsStock NameCumulative figure till date
1.1.2020Buy100A100
2.1.2020Buy50A150
5.1.2020Sell20A130

 

I am trying to compute the results in the "Cumulative figure till date" column.  Hope this clarifies.

Thanks!

Anonymous
Not applicable

[Cumulative] = // calculated column, not measure
var __stock = T[Stock Name]
var __date = T[Transaction Date]
var __result =
	SUMX(
	
		FILTER(
			T,
			T[Transaction Date] <= __date,
			T[Stock Name] = __stock
		),
		
		T[Transacted Units] * 
		(
			( T[Transaction Type] = "Buy" ) 
			-
			( T[Transaction Type] = "Sell" )
		)
	)
return
	__result

 

Best

D

Hi @RONN0014 

Cumulative figure till date =
VAR currentDate_ = Table1[Transaction Date]
VAR buyTransactions_ =
    CALCULATE (
        SUM ( Table1[Transacted units] ),
        ALLEXCEPT ( Table1, Table1[Stock Name] ),
        Table1[Transaction Date] <= currentDate_,
        Table1[Transaction Type] = "Buy"
    )
VAR sellTransactions_ =
    CALCULATE (
        SUM ( Table1[Transacted units] ),
        ALLEXCEPT ( Table1, Table1[Stock Name] ),
        Table1[Transaction Date] <= currentDate_,
        Table1[Transaction Type] = "Sell"
    )
RETURN
    buyTransactions_ - sellTransactions_

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

SU18_powerbi_badge

 

@RONN0014 

 

Try this calculated column

 

Column = SUMX(FILTER('Table','Table'[Transaction Date]<=EARLIER('Table'[Transaction Date])&&'Table'[Stock Name]=EARLIER('Table'[Stock Name])),IF('Table'[Transaction Type]="Buy",1*'Table'[Transacted units],-1*'Table'[Transacted units]))

Capture.JPG

 



Did I answer your question? Mark my post as a solution!
Appreciate with a kudos
🙂

Here is my latest blog

https://community.powerbi.com/t5/Community-Blog/Dynamic-Page-Navigation-Based-on-User-Login/ba-p/109...


Regards,
Nandu Krishna

Thank you so much guys for all for the advice! 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors