Hi everyone, I need help with one DAX for calculated column. Down below is example: I have Date, the First Opening Value and Payment (green fields). All other field depends one of another. Here is my test formula, but it doesn't work 😞test closing = var _max = MAXX(filter('Payment Schedule*','Payment Schedule*'[Opening LL 1 mesec]<>0 && 'Payment Schedule*'[Lease no]=EARLIER('Payment Schedule*'[Lease no])),'Payment Schedule*'[Start month]) var _maxamt = minx(filter('Payment Schedule*','Payment Schedule*'[Start month]=_max && 'Payment Schedule*'[Lease no]=EARLIER('Payment Schedule*'[Lease no])),'Payment Schedule*'[Opening LL 1 mesec]- 'Payment Schedule*'[Payment]+'Payment Schedule*'[Kamata 1 mesec]) var amt_previuos = CALCULATE(_maxamt, FILTER('Payment Schedule*', 'Payment Schedule*'[Start month]= DATEADD('Payment Schedule*'[Start month],-1,MONTH) && 'Payment Schedule*'[Lease no]=EARLIER('Payment Schedule*'[Lease no])))var _maxMonth = maxx(filter('Payment Schedule*','Payment Schedule*'[Start month]<=earlier('Payment Schedule*'[Start month])),'Payment Schedule*'[Start month]) return IF('Payment Schedule*'[Opening LL 1 mesec]<>0, 'Payment Schedule*'[Opening LL 1 mesec]-'Payment Schedule*'[Payment]+'Payment Schedule*'[Kamata 1 mesec], if(_maxMonth<=_max, ('Payment Schedule*'[Opening LL 1 mesec]-'Payment Schedule*'[Payment]+'Payment Schedule*'[Kamata 1 mesec]),amt_previuos +sumx(filter('Payment Schedule*','Payment Schedule*'[Start month]>_max && 'Payment Schedule*'[Start month]<=_maxMonth),(amt_previuos - 'Payment Schedule*'[Payment]) *RELATED('Lease details'[Interest rate Monthly])*'Payment Schedule*'[broj dana za obracun kamate]/30.416)) ) Thanks in advance

Hi, I think this will be easier to solve with a measure directly in the visual.

Hi astojanac , Sorry I forgot to mention that since loop is not permitted in dax calculation, we only have a workaround to do a sum calculation,for multiply,there's no good solution expect hard coding. Check my formula below: For measure： Measure = VAR _mindate = MINX ( ALL ( 'Table' ), 'Table'[Date] ) VAR _previousinterest = CALCULATE ( MAX ( 'Table'[Interest] ), FILTER ( ALL ( 'Table' ), 'Table'[Column] = MAX ( 'Table'[Column] ) - 1 ) ) VAR _opening = CALCULATE ( MAX ( 'Table'[Opening] ), FILTER ( ALL ( 'Table' ), 'Table'[Date] = _mindate ) ) VAR _startinterest = CALCULATE ( MAX ( 'Table'[Interest] ), FILTER ( ALL ( 'Table' ), 'Table'[Date] = _mindate ) ) VAR _line1 = _opening - MAX ( 'Table'[Payment] ) + ( _opening - MAX ( 'Table'[Payment] ) ) * _startinterest VAR _line2 = _line1 - MAX ( 'Table'[Payment] ) + ( _line1 - MAX ( 'Table'[Payment] ) ) * CALCULATE ( MAX ( 'Table'[Interest] ), FILTER ( ALL ( 'Table' ), 'Table'[Column] = 2 ) ) VAR _line3 = _line2 - MAX ( 'Table'[Payment] ) + ( _line2 - MAX ( 'Table'[Payment] ) ) * CALCULATE ( MAX ( 'Table'[Interest] ), FILTER ( ALL ( 'Table' ), 'Table'[Column] = 3 ) ) VAR _line4 = _line3 - MAX ( 'Table'[Payment] ) + ( _line3 - MAX ( 'Table'[Payment] ) ) * CALCULATE ( MAX ( 'Table'[Interest] ), FILTER ( ALL ( 'Table' ), 'Table'[Column] = 4 ) ) VAR _line5 = _line4 - MAX ( 'Table'[Payment] ) + ( _line4 - MAX ( 'Table'[Payment] ) ) * CALCULATE ( MAX ( 'Table'[Interest] ), FILTER ( ALL ( 'Table' ), 'Table'[Column] = 5 ) ) VAR _line6 = _line5 - MAX ( 'Table'[Payment] ) + ( _line5 - MAX ( 'Table'[Payment] ) ) * CALCULATE ( MAX ( 'Table'[Interest] ), FILTER ( ALL ( 'Table' ), 'Table'[Column] = 6 ) ) RETURN SWITCH ( MAX ( 'Table'[Column] ), 1, _line1, 2, _line2, 3, _line3, 4, _line4, 5, _line5, 6, _line6 ) For column: Column 2 = VAR _mindate = MINX ( 'Table', 'Table'[Date] ) VAR _previousinterest = CALCULATE ( MAX ( 'Table'[Interest] ), FILTER ( 'Table', 'Table'[Column] = EARLIER ( 'Table'[Column] ) - 1 ) ) VAR _opening = CALCULATE ( MAX ( 'Table'[Opening] ), FILTER ( ALL ( 'Table' ), 'Table'[Date] = _mindate ) ) VAR _startinterest = CALCULATE ( MAX ( 'Table'[Interest] ), FILTER ( ALL ( 'Table' ), 'Table'[Date] = _mindate ) ) VAR _line1 = _opening - 'Table'[Payment] + ( _opening - 'Table'[Payment] ) * _startinterest VAR _line2 = _line1 - 'Table'[Payment] + ( _line1 - 'Table'[Payment] ) * CALCULATE ( MAX ( 'Table'[Interest] ), FILTER ( 'Table', 'Table'[Column] = 2 ) ) VAR _line3 = _line2 - 'Table'[Payment] + ( _line2 - 'Table'[Payment] ) * CALCULATE ( MAX ( 'Table'[Interest] ), FILTER ( 'Table', 'Table'[Column] = 3 ) ) VAR _line4 = _line3 - 'Table'[Payment] + ( _line3 - 'Table'[Payment] ) * CALCULATE ( MAX ( 'Table'[Interest] ), FILTER ( 'Table', 'Table'[Column] = 4 ) ) VAR _line5 = _line4 - 'Table'[Payment] + ( _line4 - 'Table'[Payment] ) * CALCULATE ( MAX ( 'Table'[Interest] ), FILTER ( 'Table', 'Table'[Column] = 5 ) ) VAR _line6 = _line5 - 'Table'[Payment] + ( _line5 - 'Table'[Payment] ) * CALCULATE ( MAX ( 'Table'[Interest] ), FILTER ( 'Table', 'Table'[Column] = 6 ) ) RETURN SWITCH ( 'Table'[Column], 1, _line1, 2, _line2, 3, _line3, 4, _line4, 5, _line5, 6, _line6 ) And you will see: For the related .pbix file,pls see attached. For loop calculation I would recommend below blogs: https://community.powerbi.com/t5/Community-Blog/For-and-While-Loops-in-DAX/ba-p/636314 https://community.powerbi.com/t5/Community-Blog/Previous-Value-Recursion-in-DAX/ba-p/638320 Hope the above would help. Best Regards,Kelly Did I answer your question? Mark my post as a solution!

Hi, Here is input sample:idstart_dateend_dateopeningmonthly paymentinteres rateA101/09/201401/09/2024539,05754000.003A201/09/201601/09/2021300,50030000.0025 And here is output:ABCDEFidMonthOpeningPaymentInterestClosingA101/09/2014539,05754001601535,258A101/10/2014535,25854001590531,448A101/11/2014531,44854001578527,626A101/12/2014Closing previous month5400(Closing prev month C - payment D) * rateC+D-EA101/01/2015 5400 A101/02/2015 5400 A101/09/2024 5400 Thanks, I appreciate your expertise!

astojanac

Helper II

5 years ago

Dynamic calculated columns based on previous records

Hi everyone,

I need help with one DAX for calculated column.
Down below is example:

I have Date, the First Opening Value and Payment (green fields). All other field depends one of another.

Here is my test formula, but it doesn't work 😞

test closing =

var _max =

MAXX(

filter('Payment Schedule*',

'Payment Schedule*'[Opening LL 1 mesec]<>0 && 'Payment Schedule*'[Lease no]=EARLIER('Payment Schedule*'[Lease no])),

'Payment Schedule*'[Start month])

var _maxamt =

minx(filter('Payment Schedule*','Payment Schedule*'[Start month]=_max && 'Payment Schedule*'[Lease no]=EARLIER('Payment Schedule*'[Lease no])),

'Payment Schedule*'[Opening LL 1 mesec]- 'Payment Schedule*'[Payment]+'Payment Schedule*'[Kamata 1 mesec])

var amt_previuos = CALCULATE(_maxamt, FILTER('Payment Schedule*', 'Payment Schedule*'[Start month]= DATEADD('Payment Schedule*'[Start month],-1,MONTH) && 'Payment Schedule*'[Lease no]=EARLIER('Payment Schedule*'[Lease no])))

var _maxMonth = maxx(filter('Payment Schedule*','Payment Schedule*'[Start month]<=earlier('Payment Schedule*'[Start month])),'Payment Schedule*'[Start month])

return

IF(

'Payment Schedule*'[Opening LL 1 mesec]<>0, 'Payment Schedule*'[Opening LL 1 mesec]-'Payment Schedule*'[Payment]+'Payment Schedule*'[Kamata 1 mesec],

if(_maxMonth<=_max,

('Payment Schedule*'[Opening LL 1 mesec]-'Payment Schedule*'[Payment]+'Payment Schedule*'[Kamata 1 mesec]),

amt_previuos +

sumx(

filter('Payment Schedule*','Payment Schedule*'[Start month]>_max && 'Payment Schedule*'[Start month]<=_maxMonth),

(amt_previuos - 'Payment Schedule*'[Payment]) *

RELATED('Lease details'[Interest rate Monthly])*'Payment Schedule*'[broj dana za obracun kamate]/30.416))

)

Thanks in advance

Need Help

Show and Tell

14 Replies

Ashish_Mathur
Super User
5 years ago
Hi,

I think this will be easier to solve with a measure directly in the visual.

astojanac

Helper II

5 years ago

Hi Ashish_Mathur ,
Thank you for your reply.

May I ask you for help with measures?

Thanks in advance

Ashish_Mathur

Super User

5 years ago

Hi,

Share the exact structure of your input data in a form that i can paste in an MS Excel file.

astojanac

Helper II

5 years ago

Hi,

Here is input sample:

id	start_date	end_date	opening	monthly payment	interes rate
A1	01/09/2014	01/09/2024	539,057	5400	0.003
A2	01/09/2016	01/09/2021	300,500	3000	0.0025

And here is output:

A	B	C	D	E	F
id	Month	Opening	Payment	Interest	Closing
A1	01/09/2014	539,057	5400	1601	535,258
A1	01/10/2014	535,258	5400	1590	531,448
A1	01/11/2014	531,448	5400	1578	527,626
A1	01/12/2014	Closing previous month	5400	(Closing prev month C - payment D) * rate	C+D-E
A1	01/01/2015		5400
A1	01/02/2015		5400
A1	01/09/2024		5400

Thanks, I appreciate your expertise!

Forum Discussion

Dynamic calculated columns based on previous records

14 Replies

Related content

Dynamic Rank Calculation

Dynamic Calculation Different Period

Facing challenge to calculate previous year QTD

Dynamic Variable in Calculated Columns

Previous Month's MTD calculation