Dynamic calculated columns based on previous records

[astojanac]

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
 

[astojanac]

Hi @Ashish_Mathur , 
Thank you for your reply. 

May I ask you for help with measures?

Thanks in advance

[Ashish_Mathur]

Hi,

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

[astojanac]

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!

[v-kelly-msft]

Hi @astojanac ,

How to get the column "interest"?Based on your formula,(Opening-Payment)/100 not equal to 1601

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[astojanac]

(opening-payment) * rate * 
DATEDIFF('Payment Schedule*'[Start month],STARTOFMONTH(NEXTMONTH('Payment Schedule*'[Start month])),DAY) ----- this is days between two months (ussualy 30 or 31) 

/30.416

[v-kelly-msft]

Hi @astojanac ，

I see,but it will cause a circular dependency issue if you use 1 fixed column to get 3 dynamic columns.

Such as :

You'd better provide 2 fixed columns to calculate another 2 dynamic columns.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[astojanac]

HI @v-kelly-msft ,

I tried to change logic, so is there is chance you can help me if we only have one column/measure to calculate?

Please find sample output on following link:

https://www.dropbox.com/s/hlnfvukew1q3ah5/Sample%20data.xlsx?dl=0

Thanks, 
Alex

[v-kelly-msft]

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!

[astojanac]

Hi @v-kelly-msft , 

Thank you much, this is great!!

Now, I'm facing the problem with number of rows... I have 120 date values, excel table I sent you was just a sample.

I cant type as much rows manualy, and second thinkg, perhaps that number can increse.

So what I need is to always take last month and recalculate new one.  Is that possible?

Thanks, 

Alex

[v-kelly-msft]

Hi  @astojanac ,

As I said,if you need to do a multiply calculation in a loop,you'd better finish it outside desktop,such as SQL,then back to desktop to do a sum calculation.

Otherwise,it would be a large calculation in desktop as I show you in the last reply.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[astojanac]

Hi @v-kelly-msft , 

Exactly, that's the problem! That's why I wanted to try with calc columns not measures, but I can't firuge it out how... 

Thanks, 

Alex

[TomMartens]

Hey @astojanac ,

please allow me to point you to one of mine previous blog post, I'm sure you can adapt my approach to your requirement: Using Table Iterators to calculate a future value - Mincing Data - Gain Insight from Data (minceddat...

Regards,
Tom

[astojanac]

Hi Tom, 

Thanks for the reply, amazing blog post!!

But I'm still having issue not for one calculated value but three and I can't find way out from a circle 😞

[Ashish_Mathur]

Hi,

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