Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 😞
Thanks in advance
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.
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!
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!
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!
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:
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
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 😞
Hi,
I think this will be easier to solve with a measure directly in the visual.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |