Forum Discussion

AdriaanS's avatar
AdriaanS
New Member
9 years ago
Solved

Cumulative time slicer calc

I need to calculate the cumulative interest of an investment over various months/ years, which instead of in the Excel source data is predefined and set from the beginning of the investment period, s...
Need Help
  • TomMartens's avatar
    TomMartens
    9 years ago

    Hey Adriaan,

     

    sorry for the delay, I had to take care of some urgent family affairs, but nevertheless, here is my solution

     

     

    As you can see, the subtotal does not show what one would expect. This can be treated by a simple IF(HASONEVALUE('DimDate'[DateValue]), thecalculation, BLANK()) or by a more complex adoption of the calcualtion itself.

    The basic idea behind this solution is depicted in your Excel file (sheet: Investment), your Excel file and the pbix are

    here

     

    The solution makes use of nested TableIterator SUMX(table, PRODUCTX(table, expression)*sum('Investment'[Investment])...) and the always mind-boggling EARLIER(...)

     

    I will explain this solution in much more detail, in the next days on my blog and will post the link to my blog.

     

    I have to admit that was not the easiest one :-)

     

    Hope this helps

    Tom

TomMartens's avatar
TomMartens
Icon for Super User rankSuper User
9 years ago

Hey Adriaan,

 

not sure if I understand correctly, but I assume you have to use something similar like this DAX-statetment

 

factcum_allselected = CALCULATE(sum('Facts'[fact]);filter(ALLSELECTED('Dates'[date]);'Dates'[date] <= max('Dates'[date])))

This creates the following output:

 

You can find a little pbix-file here:

https://www.dropbox.com/sh/aj1le57q74zmcuh/AACkRZoNlnYiI-QS1FgFzTlNa?dl=0

 

Please be aware that the usage of a dedicated table reflecting the time dimension is almost mandatory.

 

Hope this helps

Tom

  • AdriaanS's avatar
    AdriaanS
    New Member
    9 years ago

    Hi Tom, that definitely helps, thank you.

     

    I've been able to get the applicable interest for the selected period by:

    Cumulative 2 = CALCULATE (
    SUM ( Investment[Investment] ),
    FILTER (
    ALLSELECTED(DimDate),
    DimDate[DateValue] <= MAX ( DimDate[DateValue] )
    )
    ) 

     

    What I'm not sure of is that within the non-linear table in PBI, how do I calculate the following: 

     

    1. 1 March 2017 - add R14, 875 to Invest Balance

    2. Calculate interest for month @ 7% 

    3. Add interest to Invest Balance

    4. 1 April 2017 - add R14, 875 to March's investment (R14,875) and that month's interest

    5. Calculate April's interest on that total

    6. Roll this calculation forward throughout the selected period range

     

    All of the above should be applicable to only the period range selected on the date slicer. 

     

    Thanks, 
    Adriaan