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.
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!
Solved! Go to Solution.
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]))
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...
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
)
)
Please mark it as solution if your like it.
Thanks,
Kaushlendra Mishra
Hi @nandukrishnavs,
Please refer to the expected table below.
Transaction Date | Transaction Type | Transacted units | Stock Name | Cumulative figure till date |
1.1.2020 | Buy | 100 | A | 100 |
2.1.2020 | Buy | 50 | A | 150 |
5.1.2020 | Sell | 20 | A | 130 |
I am trying to compute the results in the "Cumulative figure till date" column. Hope this clarifies.
Thanks!
[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
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]))
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...
Thank you so much guys for all for the advice!
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 |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
42 | |
32 | |
30 | |
18 | |
18 |