cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
astojanac
Helper II
Helper II

Dynamic calculated columns based on previous records

Hi everyone, 

 

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

astojanac_0-1627590123647.png

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
 

14 REPLIES 14
astojanac
Helper II
Helper II

Hi @Ashish_Mathur , 
Thank you for your reply. 

May I ask you for help with measures?

 

Thanks in advance

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi, 

Here is input sample:

idstart_dateend_dateopeningmonthly paymentinteres rate
A101/09/201401/09/2024539,05754000.003
A201/09/201601/09/2021300,50030000.0025

 

 

And here is output:

ABCDEF
idMonthOpeningPaymentInterestClosing
A101/09/2014539,05754001601535,258
A101/10/2014535,25854001590531,448
A101/11/2014531,44854001578527,626
A101/12/2014Closing previous month5400(Closing prev month C - payment D) * rateC+D-E
A101/01/2015 5400  
A101/02/2015 5400  
A101/09/2024 5400  
      
      



Thanks, I appreciate your expertise!

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!

(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

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 :

vkellymsft_0-1627959735255.png

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!

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

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:

vkellymsft_0-1627982079308.png

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 @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

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!

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

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!