Forum Discussion
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
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 areThe 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
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 UserHey Adriaan,
can you please share an Excel-Sheet with sample data, I will provide a more elaborate solution the next days.
Cheers
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
- TomMartens
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 areThe 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
Will definitely be keen to see your workings and explanation when you have it posted on your blog ...need to apply this somehow ;)
- TomMartens
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.
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