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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

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!