Cumulative Product on a calculated table

[vpm9119]

Hi,
I'm trying to calculate the cumulative return of a series, geting the values from 3 different related tables:
"AUX Date": which has all dates in the model
" P&L - Transparency": which has the P&L values for each date (numerator)
" NAV - Transparency": which has the NAV values for each date (denominator)

Them I create a table adding a collumn with the monthly returns, and at last I would like to calculate the cumulative (product) return for each date.

My current code returns the total return for all dates, instead to considering just the dates earlier to that specific row:

VAR SelectedDate = SELECTEDVALUE('AUX Dates'[Date])


VAR tbl_data =
    ADDCOLUMNS(
        SUMMARIZE('AUX Dates',
            'AUX Dates'[Date]),
        "P&L", SUMX(
                    FILTER('P&L - Transparency',
                    'P&L - Transparency'[Date] = 'AUX Dates'[Date]),        
                'P&L - Transparency'[P&L Transparency]),
        "NAV BoP", SUMX(
                    FILTER('NAV - Transparency',
                    'NAV - Transparency'[Date] = 'AUX Dates'[Date]),        
                'NAV - Transparency'[NAV Transparency BoP]))

VAR tbl_result = 
    ADDCOLUMNS(tbl_data,
    "Return Monthly", DIVIDE([P&L],[NAV BoP],0),
    "Return Monthly Factor", 1 + DIVIDE([P&L],[NAV BoP],0))

VAR tbl_results = 
    ADDCOLUMNS(tbl_result,
    "Product", CALCULATE(
                    PRODUCTX(tbl_result,
                        [Return Monthly Factor]),
                        ALLSELECTED('AUX Dates'),'AUX Dates'[Date] <= SelectedDate))
   
RETURN
tbl_results

The current results are:

Thank you in advance for any help!

[Anonymous]

Hi @vpm9119 

I have made some changes to your dax so you can try if that solves the problem:

var tbl_result = 
ADDCOLUMNS(
    tbl_result, 
    "Product", 
    CALCULATE(
        SUM([Return Monthly Factor]), 
        ALLSELECTED('AUX Dates'), 
        'AUX Dates'[date] <= SelectedDate
    )
)
RETURN tbl_result

If you still have problems, it is best to provide the pbix file and be careful to delete sensitive data.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.