Forum

Compounding percent...
 
Notifications
Clear all

Compounding percentage in a pivot table

20 Posts
3 Users
0 Reactions
347 Views
Riny van Eekelen
(@riny)
Posts: 1194
Member Moderator
 

Now, I must admit that I'm not at all comfortable explaining DAX measures. Although, the attached workbook does what you need (I believe) by adding the year into the FILTER, I feel that there must be a better way to do this.

 
Posted : 24/11/2023 11:46 am
(@andyc)
Posts: 20
Eminent Member
Topic starter
 

Thank you,

You've been very helpfull, that looks like it works the way I'd like.

I'll keep studying and doing examples, hopefully it will start to click.

 
Posted : 24/11/2023 12:03 pm
(@debaser)
Posts: 837
Member Moderator
 

If you create a date table, you can then use time intelligence functions. You can create a compounding measure (Compounded Percent) using:

 =VAR CP =PRODUCTX(Table2, 1+Table2[%gain])
RETURN IF(ISBLANK(CP),BLANK(),CP-1)

 

then your other two measures are:

CompoundYTD

=IF(COUNTROWS(Table2)>0,TOTALYTD([Compounded Percent],'Calendar'[Date]),BLANK())

(the COUNTROWS is just to hide values for months where you don't have data)

CompoundMTD

=TOTALMTD([Compounded Percent],'Calendar'[Date])

 
Posted : 25/11/2023 4:38 am
Riny van Eekelen
(@riny)
Posts: 1194
Member Moderator
 

Thx to @Velouria!!

I've added the time intelligent solution to the file.

 
Posted : 25/11/2023 6:29 am
(@andyc)
Posts: 20
Eminent Member
Topic starter
 

Well, I'm certainly learning a lot here!

Thanks very much Velouria and Riny

 
Posted : 25/11/2023 1:23 pm
Page 2 / 2
Share: