How to calculate return of investment in powerBI using DAX?

Question

Hi everyone,

I have 2 columns as shown in the screenshot above, Date and Cumsum P/L. I want to calculate the return per month by using this formula:

(Last Cumsum P/L of the month - First Cumsum P/L of the month) / First Cumsum P/L of the month

For example, in Nov 2021, the return will be ($3738.58-$3615.06)/$3615.06 = 3.42%

I want to get the return for all the months and plot them in a line chart. Ideally if possible, when I filter the data by daily, the return will be based on daily basis, and if I filter the data by monthly or yearly, it will give me monthly or yearly return. Any help or advise will be greatly appreciated!

Angelo Canepa · Accepted Answer · 2022-01-13T10:33:27.037

Assuming your data looks like this

Date	Amount
02 August 2021	19
05 August 2021	18
31 August 2021	25
01 September 2021	29
05 September 2021	23
30 September 2021	31
04 October 2021	30
05 October 2021	30
31 October 2021	31
01 November 2021	38
05 November 2021	38
30 November 2021	44
01 December 2021	45
05 December 2021	42
31 December 2021	42

I have created a Return calculation, assuming you have a Calendar table. I have specified the DAX for the calendar table and the calculation below.

The idea of the calculation is to get the first day of the month value and the last day of the month value and use those to get the return of each month.

The calculation works using as x-axis the field Month and Month Truncated.

It's better practice to use a Calendar table because the number of scans done by the engine are fairly reduced, in contrast, to scan a fact table. Also, you can rely on the time intelligence formulas in DAX when you use a calendar table.

DAX: Calendar Table

Calendar =
ADDCOLUMNS (
    CALENDAR ( MIN ( 'Table'[Date] ), MAX ( 'Table'[Date] ) ),
    "Month", MONTH ( [Date] ),
    "Month Truncated", DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 01 )
)

DAX: Return Calculation

Return =
Return =
VAR CurrentMonth =
    SELECTEDVALUE ( 'Calendar'[Month] )
VAR FirstDayMonth =
    CALCULATE (
        MIN ( 'Calendar'[Date] ),
        'Calendar'[Month] = CurrentMonth,
        'Calendar'[Date] IN VALUES ( 'Table'[Date] )
    )
VAR LastDayMonth =
    CALCULATE (
        MAX ( 'Calendar'[Date] ),
        'Calendar'[Month] = CurrentMonth,
        'Calendar'[Date] IN VALUES ( 'Table'[Date] )
    )
VAR FirstDayMonthValue =
    CALCULATE ( MAX ( 'Table'[Amount] ), 'Table'[Date] = FirstDayMonth )
VAR LastDayMonthValue =
    CALCULATE ( MAX ( 'Table'[Amount] ), 'Table'[Date] = LastDayMonth )
VAR ReturnCalculation =
    DIVIDE ( LastDayMonthValue - FirstDayMonthValue, FirstDayMonthValue )
RETURN
    ReturnCalculation

Output

Calendar Table as Date Table

Step 1

Step 2

Hi Angelo, thanks for your reply, my Date column is not unique, so I cant mark as date table — weizer, Jan 13 '22 at 01:20
You need to create an additional table called Calendar. Marking the date column is optional for this calculation but recommended as good practice. — Angelo Canepa, Jan 13 '22 at 01:41
Based on the screenshot in my question, the first day of the month for November in my date column is actually 4th Nov 2021 instead of 1st Nov 2021. I realized the DAX only work when the first day of the month in my date column is 1st Nov, is there any way to make it more flexible? — weizer, Jan 13 '22 at 02:38
@weizer I've edited the data and the calculation to take into account the flexible approach. Please mark as a solution the answer if it was helpful. — Angelo Canepa, Jan 13 '22 at 10:35