Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
Shares Balance =
CALCULATE(
SUM( fTrans[Shares] ),
ALLEXCEPT( fTrans, fTrans[Ticker] ),
fTrans[Transaction] = "Purchase",
fTrans[Date] <= MAX( fTrans[Date] )
) -
CALCULATE(
SUM( fTrans[Shares] ),
ALLEXCEPT( fTrans, fTrans[Ticker] ),
fTrans[Transaction] = "Sale",
fTrans[Date] <= MAX( fTrans[Date] )
)
Solved! Go to Solution.
Hi, I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.
expected result measure: =
VAR _t =
ADDCOLUMNS (
SUMMARIZE (
ALL ( fTrans ),
fTrans[Ticker],
fTrans[Date],
fTrans[Transaction],
fTrans[Shares]
),
"@transindex",
SWITCH (
TRUE (),
fTrans[Transaction] = "Sale", 2,
fTrans[Transaction] = "Purchase", 1
),
"@transvalue",
SWITCH (
TRUE (),
fTrans[Transaction] = "Sale", CALCULATE ( SUM ( fTrans[Shares] ) ) * -1,
fTrans[Transaction] = "Purchase", CALCULATE ( SUM ( fTrans[Shares] ) )
)
)
RETURN
IF (
HASONEVALUE ( fTrans[Ticker] ),
SUMX (
WINDOW (
1,
ABS,
0,
REL,
_t,
ORDERBY ( fTrans[Date], ASC, [@transindex], ASC ),
,
PARTITIONBY ( fTrans[Ticker] )
),
[@transvalue]
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi, I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.
expected result measure: =
VAR _t =
ADDCOLUMNS (
SUMMARIZE (
ALL ( fTrans ),
fTrans[Ticker],
fTrans[Date],
fTrans[Transaction],
fTrans[Shares]
),
"@transindex",
SWITCH (
TRUE (),
fTrans[Transaction] = "Sale", 2,
fTrans[Transaction] = "Purchase", 1
),
"@transvalue",
SWITCH (
TRUE (),
fTrans[Transaction] = "Sale", CALCULATE ( SUM ( fTrans[Shares] ) ) * -1,
fTrans[Transaction] = "Purchase", CALCULATE ( SUM ( fTrans[Shares] ) )
)
)
RETURN
IF (
HASONEVALUE ( fTrans[Ticker] ),
SUMX (
WINDOW (
1,
ABS,
0,
REL,
_t,
ORDERBY ( fTrans[Date], ASC, [@transindex], ASC ),
,
PARTITIONBY ( fTrans[Ticker] )
),
[@transvalue]
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@Jihwan_Kim your solution works perfectly, thanks a lot!
There is one particular thing though about a table visual I've got on my Power BI data model.
This particular visual contains 2 date columns: the fact date called "Data" which is the date each transaction took place and a second column that I need for another purpose not worth explaining.
The thing is as you can see on the screenshot below when the visual brings both dates columns the column with the shares balance measure you helped me create (here called "Saldo de cotas") ends up bringing weird results:
As soon as I remove that second date column "Data da venda relacionada à respectiva compra" from the visual the shares balance measure is back to normal, bringing the results that are meant to come.
Is there a way to add an element to the DAX code that could shield the solution from that second dates column? Something like ALL or ALLSELECTED somewhere in the formula so once that particular table visual comes with both dates columns the measure column still brings the correct results?
User | Count |
---|---|
25 | |
21 | |
20 | |
13 | |
13 |
User | Count |
---|---|
40 | |
28 | |
27 | |
23 | |
21 |