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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
KMLAI
Frequent Visitor

Dynamic cumulation of calculated columns

KMLAI_0-1694510038552.png

'Cumulative Amount' and 'Shared Amount' are calculated column that

Cumulative Amount =

VAR FTBO = FILTER(Table,Table[Name]=EARLIER(Table[Name])
            &&Table[ISSUE_DATE]<EARLIER(Table[ISSUE_DATE])
            )

VAR FTBT = FILTER(Table,Table[Name]=EARLIER(Table[Name])
            &&Table[ISSUE_DATE]=EARLIER(Table[ISSUE_DATE])
            &&Table[Index by Issue Date]<EARLIER(Table[Index by Issue Date])
            )

return
SUMX(FTBT,[Amount])+SUMX(FTBO,[Amount])+[Amount]

Shared Amount= if ([Cumulative Amount]>[Retention],[Share%]*[Amount],0)

 

What I actually want is to also take out the cumulative 'Shared Amount' from 'Cumulative Amount' so that

G4 =  3,000,000 -  750,000 =  2,250,000; H4 = 2,250,000*0.5 =  1,125,000 

and G5 =  7,909,130 - 1,125,000 -  750,000 =  6,034,130; H5 = 6,034,130* 0.5 =  3,017,065 

 

KMLAI_1-1694510069523.png

So I edited the last line of Cumulative Amount formula to

return

SUMX(FTBT,[Amount])+SUMX(FTBO,[Amount])+[Amount]-SUMX(FTBT,[Shared Amount])-SUMX(FTBO,[Shared Amount])

 

But it will hit the circular dependency error. I understand how it hit the circular dependency error if I am only looking at the same line but the table has already been filtered to exclude the current row so there should not be any circular reference.

 

Is it possible to be solved?

Thank you very much in advance for your help.

2 REPLIES 2
v-zhangti
Community Support
Community Support

Hi, @KMLAI 

 

You can try the following methods.

Previous Shared Amount = 
Var _N1=MAXX(FILTER('Table',[Name]=EARLIER('Table'[Name])&&[ISSUE_DATE]<EARLIER('Table'[ISSUE_DATE])),[Shared Amount])
Var _N2=MAXX(FILTER('Table',[Name]=EARLIER('Table'[Name])&&[ISSUE_DATE]=EARLIER('Table'[ISSUE_DATE])&&[Index by Issue Date]<EARLIER('Table'[Index by Issue Date])),[Shared Amount])
Return
_N1+_N2
Cumulative Amount2 = [Cumulative Amount]-[Previous Shared Amount]
Shared Amount2 = IF([Cumulative Amount2]>[Retention],[Share%]*[Cumulative Amount2],0 )

vzhangti_0-1695629230624.png

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks @v-zhangti.

the row 3 is same as expected but the row 4 is not as the [Cumulative Amount2] is referring to the [Shared Amount] instead of [Shared Amount2]. (i.e. It should be 7,909,130 - 1,125,000 - 750,000 = 6,034,130 while now we have 7909130 - 1500000 = 6409130)

By adding column means if I have 20 rows then I will 19*2 more columns.

Is there a simplifying way to do that?

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.