Forum Discussion

vpm9119's avatar
vpm9119
Regular Visitor
2 years ago

Cumulative Product on a calculated table

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!

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.