cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
AdriaanS Frequent Visitor
Frequent Visitor

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, should reset based on the timeline slicer date range chosen.

 

- The Excel clip represents the initial source data loaded into PBI, which commences 04/05/2015

- If I change the slicer in PBI to 23/02/2017 - 31/12/2019 it gives me the cumulative interest from 04/05/2015, but I would like to see what the cumulative interest will be for the period selected, instead of the total period. 

 - To further add to the complexity I would like to add a monthly deposit to the investment, which should be used to calculate that particular month's interest ...cumulatively over time.

 

I gather I can do this with DAX filters and/ or variables somehow, but need guidance as to how to do it, please.

 

Slicer.PNG

 

Inv source data.PNGInv PBI.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Cumulative time slicer calc

Hey Adriaan,

 

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

 

almost.png

 

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

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
10 REPLIES 10
Super User
Super User

Re: Cumulative time slicer calc

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:

2017-02-23_16-19-21.png

 

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

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
AdriaanS Frequent Visitor
Frequent Visitor

Re: Cumulative time slicer calc

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

 

Super User
Super User

Re: Cumulative time slicer calc

Hey Adriaan,

 

can you please share an Excel-Sheet with sample data, I will provide a more elaborate solution the next days.

 

Cheers

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
AdriaanS Frequent Visitor
Frequent Visitor

Re: Cumulative time slicer calc

Hi Tom, 

 

That would be fantastic, thanks. 

 

I've dropped the source Excel data and .pbix in Dropbox for you: https://www.dropbox.com/sh/lldx2b945ihs5bg/AACaDhCcoVYSK5H4cJRrxBUoa?dl=0

 

Cheers,

Adriaan

 

Super User
Super User

Re: Cumulative time slicer calc

Hey Adriaan,

 

sorry for the delay, but I had to take care of something different for the last couple of days. Just downloaded your sample files.

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Super User
Super User

Re: Cumulative time slicer calc

Hey Adriaan,

 

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

 

almost.png

 

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

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
AdriaanS Frequent Visitor
Frequent Visitor

Re: Cumulative time slicer calc

Good morning Tom, 

 

Sorry for having gone quiet - been running around with work. 

 

Fantastic! I really appreciate your input and will hopefully get to go through all the detail and provide feedback soon. 

 

Kind regards, 

Adriaan

AdriaanS Frequent Visitor
Frequent Visitor

Re: Cumulative time slicer calc

Will definitely be keen to see your workings and explanation when you have it posted on your blog ...need to apply this somehow Smiley Wink 

Super User
Super User

Re: Cumulative time slicer calc

Finally, here is a much more elaborated explanation of a solution for the problem and it also contains an additional twist, using an interest rate coming from a separate table:

 

https://docs.com/minceddata/3687/dax-using-iterations-to-calculate-a-future-value?c=B13yYP

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 394 members 3,662 guests
Please welcome our newest community members: