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!
I'm looking for this solution a long time, but i did not find it yet. Let's go:
I have a measure calculating the return of a portfolio day by day. It looks like this:
Now, i have to multiply and accumulate the values, day by day. It will look like this:
A simple DAX formula would do the job, like Product(Measure). However, "PRODUCT" formula only accept columns, not measures.
I found another DAX formula:
Solved! Go to Solution.
I have had a look at your file and existing measures, and uploaded an edited copy with suggested measures here.
Performance seemed acceptable.
Could you could confirm measures are producing correct results (I may well have missed something in the logic!) and whether performance is acceptable for you.
I would recommend measures rather than calculated columns to handle both the Cota and Cumulative Cota calculations. With measures, the "return" calculation can adjust based on all filters, which would be impossible with calculated columns.
It appears that the basic calculation you are wanting to produce is "time-weighted return", in this case expressed as a ratio, i.e. 1 + rate of return.
The updates I made were:
Balance Final = VAR MaxDateFilter = MAX ( CalendarioDAX[Date] ) RETURN CALCULATE ( SUM ( BaseFinal[Saldo Final] ), CALCULATETABLE ( LASTDATE ( SUMMARIZE ( BaseFinal, CalendarioDAX[Date] ) ), CalendarioDAX[Date] <= MaxDateFilter ) ) Balance Initial = CALCULATE ( [Balance Final], PREVIOUSDAY( CalendarioDAX[Date] ) ) Movement = SUM ( BaseFinal[Valor Movimentado] ) Cota (Daily) = PRODUCTX ( VALUES ( CalendarioDAX[Date] ), DIVIDE ( [Balance Final] - [Movement], [Balance Initial], 1 ) ) Cota Cumulative (Daily) = VAR MinDate = MIN ( CalendarioDAX[Date] ) VAR DataGlobalMaxDate = CALCULATE ( MAX ( BaseFinal[Data] ), ALL ( CalendarioDAX ) ) RETURN IF ( MinDate <= DataGlobalMaxDate, VAR MinDateAllselected = CALCULATE ( MIN ( CalendarioDAX[Date] ), ALLSELECTED ( CalendarioDAX ) ) VAR MaxDate = MAX ( CalendarioDAX[Date] ) RETURN CALCULATE ( [Cota (Daily)], DATESBETWEEN ( CalendarioDAX[Date], MinDateAllselected, MaxDate ) ) )
Regards,
Owen
Dear @OwenAuger
Thank you so much for the extremely helpful information and for sharing the pbi report above.
I am trying to create a report similar to the one you shared above but based on my table containing the following columns: date, stock symbol, open price, close price, cashflow, stock balance change (# of stocks bought/sold). Note that the final daily balance (same as "BaseFinal[Saldo Final]" in your report) is not in the table and thus I believe should be calculated either as a column or a measure in order to create "Balance Final" measure you mentioned above (also copied below for reference). Thanks to the report you shared, I figured out a way to calculate the cumulative number of stocks owned on a each day for each stock symbol ("Stocks Cumulative (Daily)" measure below). However, I am unable to calculate a final daily balance (basically "close price"*"Stocks Cumulative (Daily)") for each symbol that would aggregate correctly. Considering this, could you please recommend the best way to calculate final balance ($$) for each symbol with a daily granularity? (I believe the simplest way would be to transform "Stocks Cumulative (Daily)" measure into a calculated column and then adding another calculated column to multiple "Stocks Cumulative (Daily)" by "close price", but have no idea how to do it or if it is even feasible).
My measure to calculate the cumulative number of stocks owned on a each day for each stock symbol (based on your "Cota Cumulative (Daily)" measure):
Stocks Cumulative (Daily) =
VAR MinDate =
MIN ( CalendarioDAX[Date] )
VAR DataGlobalMaxDate =
CALCULATE ( MAX ( DailyStockPrices[Date] ), ALL ( CalendarioDAX ) )
RETURN
IF (
MinDate <= DataGlobalMaxDate,
VAR MinDateAllselected =
CALCULATE ( MIN ( CalendarioDAX[Date] ), ALL ( CalendarioDAX ) )
VAR MaxDate =
MAX ( CalendarioDAX[Date] )
RETURN
CALCULATE (
SUM(DailyStockPrices[StockBalanceChange),
DATESBETWEEN ( CalendarioDAX[Date], MinDateAllselected, MaxDate )
)
)
Your original measure (just for reference) that I am trying to replicate, but cannot (due to lack of BaseFinal[Saldo Final] column in my table):
Balance Final = VAR MaxDateFilter = MAX ( CalendarioDAX[Date] ) RETURN CALCULATE ( SUM ( BaseFinal[Saldo Final] ), CALCULATETABLE ( LASTDATE ( SUMMARIZE ( BaseFinal, CalendarioDAX[Date] ) ), CalendarioDAX[Date] <= MaxDateFilter ) )
Thank you so much in advance!
@Anonymous
I need a measure because a calculated column does not work in this case. Let me explain:
I have 2 different investments in 2 differentes banks at the same day:
Bank 1 = 10k
Bank 2 = 20k
Total: 30k
If I want to apply a filter only in bank 1 = 10k, the calculated column already has the value 30k. I tried to do a variable calculated column, but I don’t know how to do that (recalculate the column everytime a filter is applied).
Yes, is only in visible days. The problem is I’m not familiar with Power Query.
The problem goes on...
I have had a look at your file and existing measures, and uploaded an edited copy with suggested measures here.
Performance seemed acceptable.
Could you could confirm measures are producing correct results (I may well have missed something in the logic!) and whether performance is acceptable for you.
I would recommend measures rather than calculated columns to handle both the Cota and Cumulative Cota calculations. With measures, the "return" calculation can adjust based on all filters, which would be impossible with calculated columns.
It appears that the basic calculation you are wanting to produce is "time-weighted return", in this case expressed as a ratio, i.e. 1 + rate of return.
The updates I made were:
Balance Final = VAR MaxDateFilter = MAX ( CalendarioDAX[Date] ) RETURN CALCULATE ( SUM ( BaseFinal[Saldo Final] ), CALCULATETABLE ( LASTDATE ( SUMMARIZE ( BaseFinal, CalendarioDAX[Date] ) ), CalendarioDAX[Date] <= MaxDateFilter ) ) Balance Initial = CALCULATE ( [Balance Final], PREVIOUSDAY( CalendarioDAX[Date] ) ) Movement = SUM ( BaseFinal[Valor Movimentado] ) Cota (Daily) = PRODUCTX ( VALUES ( CalendarioDAX[Date] ), DIVIDE ( [Balance Final] - [Movement], [Balance Initial], 1 ) ) Cota Cumulative (Daily) = VAR MinDate = MIN ( CalendarioDAX[Date] ) VAR DataGlobalMaxDate = CALCULATE ( MAX ( BaseFinal[Data] ), ALL ( CalendarioDAX ) ) RETURN IF ( MinDate <= DataGlobalMaxDate, VAR MinDateAllselected = CALCULATE ( MIN ( CalendarioDAX[Date] ), ALLSELECTED ( CalendarioDAX ) ) VAR MaxDate = MAX ( CalendarioDAX[Date] ) RETURN CALCULATE ( [Cota (Daily)], DATESBETWEEN ( CalendarioDAX[Date], MinDateAllselected, MaxDate ) ) )
Regards,
Owen
Man, it worked like a miracle. That was exaclty what I was looking for.
Thank you so much!
Sure, here it goes the file.
https://mega.nz/#!WaQylKrY!tGJrKhhN7ai9haKMWD7LDLr6C6agtNx4Gqt0ppzLkiA
Regards,
Hi @marcellope
Can you share the definition of your [Cota] measure?
Also a PBIX with dummy data or a diagram of the data modelwould be useful.
Depending how [Cota] is defined, we may be able to simplify the calculation of the compounded return that you are trying to produce.
Regards,
Owen
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 |
---|---|
43 | |
21 | |
20 | |
15 | |
13 |
User | Count |
---|---|
45 | |
41 | |
39 | |
19 | |
19 |