How to calculate return of investment in powerBI using DAX?

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!

Assuming your data looks like this

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