Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |