Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AdriaanS
New Member

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

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

10 REPLIES 10

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.